Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

XML Workshop 25 - Inserting elements and attributes to an XML document Expand / Collapse
Author
Message
Posted Sunday, November 28, 2010 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 12:16 AM
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
Post #1027037
Posted Sunday, November 28, 2010 5:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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?

.
Post #1027041
Posted Sunday, November 28, 2010 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 12:16 AM
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
Post #1027043
Posted Friday, December 9, 2011 4:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 3:16 AM
Points: 28, 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>'
Post #1219220
Posted Friday, December 9, 2011 4:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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


.
Post #1219233
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse