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

Question

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

jacob sebastian

SSChampion

Points: 11822
More actions
March 17, 2010 at 10:26 pm

#156793

Comments posted to this topic are about the item XML Workshop 25 - Inserting elements and attributes to an XML document
.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

Brad Schulz Old Hand Points: 395 More actions · Answer 3

Brad Schulz

Old Hand

Points: 395

March 18, 2010 at 11:14 am

#1136172

Wow! Excellent stuff, Jacob.

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

Thank you Paul, Jason and Brad. I am glad to know that you liked it.

.

fabio.sorci SSC Journeyman Points: 98 More actions · Answer 5

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 6

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.

.

fabio.sorci SSC Journeyman Points: 98 More actions · Answer 7

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 8

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:

http://beyondrelational.com/blogs/jacob/archive/2009/03/15/sql-server-xsd-typed-xml-and-schema-collections.aspx

http://beyondrelational.com/blogs/jacob/archive/2009/03/10/sql-server-xsd-validating-values-with-schema.aspx

.

pham_p Valued Member Points: 58 More actions · Answer 9

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

liya.tansky SSC Enthusiast Points: 100 More actions · Answer 10

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 = '

<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

/*

<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 SSChampion Points: 11822 More actions · Answer 11

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 SSC Enthusiast Points: 100 More actions · Answer 12

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 SSC Enthusiast Points: 156 More actions · Answer 13

I have one XML

<Root>

</Root>

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

<Root>

</Students>

<Posts>

</Posts>

</Root>'

jacob sebastian SSChampion Points: 11822 More actions · Answer 14

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

.