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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 9,928, Visits: 11,203
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 18,054, Visits: 16,085
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
Post #885296
Posted Thursday, March 18, 2010 11:14 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:45 PM
Points: 20, Visits: 236
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 1, 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 1, 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 3, 2010 12:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 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