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 Wednesday, March 17, 2010 10:26 PM
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
Comments posted to this topic are about the item XML Workshop 25 - Inserting elements and attributes to an XML document

.
Post #885210
Posted Thursday, March 18, 2010 1:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 11,168, Visits: 10,929
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #885286
Posted Thursday, March 18, 2010 1:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 20,458, Visits: 14,079
Nice article. Thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #885296
Posted Thursday, March 18, 2010 11:14 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 28, 2014 3:47 PM
Points: 20, Visits: 231
Wow! Excellent stuff, Jacob.
Post #885760
Posted Friday, March 19, 2010 6:55 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
Thank you Paul, Jason and Brad. I am glad to know that you liked it.

.
Post #886316
Posted Wednesday, March 31, 2010 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 26, 2010 12:31 AM
Points: 8, Visits: 31
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 INT, @i INT, @xml XML
SELECT @xml=
(SELECT * FROM OPENROWSET(BULK
'C:\MYXML.xml'
, SINGLE_BLOB) as x)
SELECT
@max = @xml.query('<e>
{ count(/flsProSoc/Accesso) }
</e>').value('e[1]','int')
SET @i = 1
WHILE @i <= @max 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
Post #893551
Posted Wednesday, March 31, 2010 4:05 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
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.



.
Post #893597
Posted Thursday, April 01, 2010 2:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 26, 2010 12:31 AM
Points: 8, Visits: 31
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
Post #894503
Posted Thursday, April 01, 2010 10:34 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
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


.
Post #895041
Posted Monday, May 03, 2010 12:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 5:55 PM
Points: 2, Visits: 204
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
Post #914885
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse