Insert xml VS2008

  • Thread starter Thread starter AMP
  • Start date Start date
A

AMP

Hello,
I am trying (for the first time) to create an insert statement to
insert xml into an sql2005 db with a datatype of "xml'.
When I try to use the query builder to create an Insert Statement, it
doesnt let me mark any on my xml fields for inclusion in the query.
How come?
Thanks
 
Are you sending xml "in" and storing the entire xml in a column in a table?

Or are you trying to parse (shred) the xml and get at individual values (or
rows of data)?

............

See:
http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html
for shredding the data.


Here is a simple INSERT.......replace the #temp table (called #holder) with
your "real" table.




DECLARE @data XML;




SET @data =

N'<data>

<customer>

<id>1</id>

<name>Allied Industries</name>

</customer>

<customer>

<id>2</id>

<name>Trades International</name>

</customer>

</data>';



IF OBJECT_ID('tempdb..#holder') IS NOT NULL

begin

drop table #holder

end

Create Table #holder (

HolderKey int identity(1,2) ,

MyXml xml

)



INSERT into #holder (MyXml)

values (@data)


select * from #holder



IF OBJECT_ID('tempdb..#holder') IS NOT NULL

begin

drop table #holder

end
 
Are you sending xml "in" and storing the entire xml in a column in a table?

Or are you trying to parse (shred) the xml and get at individual values (or
rows of data)?

...........

See:http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005....
for shredding the data.

Here is a simple INSERT.......replace the #temp table (called #holder) with
your "real" table.

DECLARE @data XML;

SET @data =

N'<data>

<customer>

<id>1</id>

<name>Allied Industries</name>

</customer>

<customer>

<id>2</id>

<name>Trades International</name>

</customer>

</data>';

IF OBJECT_ID('tempdb..#holder') IS NOT NULL

begin

drop table #holder

end

Create Table #holder (

HolderKey int identity(1,2) ,

MyXml xml

)

INSERT into #holder (MyXml)

values (@data)

select * from #holder

IF OBJECT_ID('tempdb..#holder') IS NOT NULL

begin

drop table #holder

end






- Show quoted text -
Click to expand...

I'm tryin to insert a string that is well formed xml.
 
Ooops.

I got csharp and sqlserver.programming newsgroups mixed up.


Sorry........


My advice is .......... using the query builder is a tool......but not a
be-all. write up some code manually.
 
Back
Top