March 17, 2010 at 10:26 pm
Comments posted to this topic are about the item XML Workshop 25 - Inserting elements and attributes to an XML document
.
March 18, 2010 at 1:11 am
Another great instalment.
One thing for the section on inserting the contents of an XML variable when using 2005: there is a good workaround by Denis Ruckebusch here.
March 18, 2010 at 1:50 am
Nice article. Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 11:14 am
Wow! Excellent stuff, Jacob.
March 19, 2010 at 6:55 am
Thank you Paul, Jason and Brad. I am glad to know that you liked it.
.
March 31, 2010 at 2:47 am
Hello jacob
The question is this:
I need to add a progressive for each element of an XML document. I read your two interesting articles "XML Workshop 25" and "XML Workshop XVII - Writing a LOOP to process XML elements in TSQL" and merged the two.
The problem is this:
Until I have a few XML elements from loop (say up to 10) all right, but increasing the processing is very slow so that I stop.
xml docoment:
<flsProSoc>
<Accesso>
<Identificativo>2008123456789000</Identificativo>
<Erogatore>
<CodiceIstituto>010ABAZZ</CodiceIstituto>
</Erogatore>
<Entrata>
<Data>2008-03-18</Data>
<Ora>09:30</Ora>
</Entrata>
<!--
many other XML elements and subelements XML
-->
</Accesso>
.......................................
.......................................
</flsProSoc>
Code:
DECLARE @max-2 INT, @i INT, @xml XML
SELECT @xml=
(SELECT * FROM OPENROWSET(BULK
'C:\MYXML.xml'
, SINGLE_BLOB) as x)
SELECT
@max-2 = @xml.query('<e>
{ count(/flsProSoc/Accesso) }
</e>').value('e[1]','int')
SET @i = 1
WHILE @i <= @max-2 BEGIN
SET @xml.modify('
insert element MyId {sql:variable("@i")}
as first
into (flsProSoc/Accesso[position()=sql:variable("@i")])[1]
')
SET @i = @i + 1
END
I thank you in advance for your interest
March 31, 2010 at 4:05 am
I think the performance is bad here for two reasons: loops and UNTYPED XML. In most cases, loops give bad performance. Since the XML document being processed is not TYPED XML, the processing overhead will be comparatively more.
.
April 1, 2010 at 2:19 am
Hi Jacob
Typed XML want to XSD schema associated, right?
I try to see if it improves performance.
Where do I specify the XSD file?
There is another solution to my problem always returns within TSQL?
Thanks again
April 1, 2010 at 10:34 am
In SQL Server, you cannot specify XSD file to create a TYPED XML document, instead you need to create an XML SCHEMA Collection and then associate an XML document with a schema collection.
The following articles might help you to get started with XML Schema Collections:
.
May 3, 2010 at 12:43 pm
Excellent article. I've been wondering on what is the best approach for updating an XML field data given another XML field with the same schema.
Thanks
November 28, 2010 at 5:17 am
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
November 28, 2010 at 5:42 am
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?
.
November 28, 2010 at 5:49 am
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
December 9, 2011 at 4:18 am
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>'
December 9, 2011 at 4:37 am
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
.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy