SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop 25 - Inserting elements and attributes to an XML document


XML Workshop 25 - Inserting elements and attributes to an XML document

Author
Message
liya.tansky
liya.tansky
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 54
Hi,
The last example in the article is not working, gives an error:
XQuery: SQL type 'xml' is not supported in XQuery.

THe example is:
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
</Employees>'

DECLARE @emp XML
SELECT @emp = '<Employee Team="SQL Server">Steve</Employee>'

SET @x.modify('
insert sql:variable("@emp")
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
</Employees>
*/

PS IRRELEVANT. Works in 2008 and needs dynamic sql in 2005
jacob sebastian
jacob sebastian
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 2523
The last example clearly mentions that it will work only on SQL Server 2008 and above. Are you running the example on SQL Server 2005?

.
liya.tansky
liya.tansky
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 54
Yes, I'm sorry. Just got it and didn't had time to reply.
Thanks for the fast reply.
I'll try to update my first comment or remove it
pk_nirmal
pk_nirmal
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 52
I have one XML

<Root>
<Student>Jhon </Student>
<Student> Luka </Student>

<Post>1</Post>
<Post>2</Post>
</Root>

Is it possible to add root node called Students for Student section and Posts for Post, as given below?

<Root>
<Students>
<Student>Jhon </Student>
<Student> Luka </Student>
</Students>
<Posts>
<Post>1</Post>
<Post>2</Post>
</Posts>
</Root>'
jacob sebastian
jacob sebastian
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 2523
This can be achieved by a FLWOR operation.

DECLARE @x XML = '
<Root>
<Student>Jhon </Student>
<Student> Luka </Student>
<Post>1</Post>
<Post>2</Post>
</Root>'

SELECT @x.query ('
for $i in (Root)
let $s := $i/Student
let $p := $i/Post
return
<Root>
<Students> {$s} </Students>
<Posts>{$p}</Posts>
</Root>
')

/*
Produces:
<Root>
<Students>
<Student>Jhon </Student>
<Student> Luka </Student>
</Students>
<Posts>
<Post>1</Post>
<Post>2</Post>
</Posts>
</Root>
*/



This is one of those scenarios where the FLWOR operation is quite handy. I just added this example to the XQuery Labs

.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search