Insert xml VS2008

[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

 

[sloan]

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

 

[AMP]

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 -

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

 

[sloan]

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.