shredding xml in sql server 2005

5 pts.
Tags:
SQL Server 2005
XML
In SQL Server 2005 one shreds XML via the nodes() method. When inserting into a table using this it preforms extremely slowly. If the "into #x" is removed the shredding is very fast.
declare @x xml
-- there are 1000 rows in the xml document
set @xml = '
<rows>
  <row>
    <guid>C6EF3618-AAE1-FB4B-825D-3E3633F9A398</guid>
    <rdn>dc=net,dc=my,ou=a,ou=test</rdn>
    <domainName>test domain</domainName>
    <details>this is a very nasty test with many details.</details>
    <parentGuid>4F79DD5D-70D6-9D44-A16C-6365FA33D3B1</parentGuid>
  </row>
<row>...</row>
...
</rows>
'

SELECT	 
	 t.col.value ('guid [1]', 'UNIQUEIDENTIFIER')		AS guid
	,t.col.value ('rdn [1]', 'varchar (1024)')				AS rdn
	,t.col.value ('domainName [1]', 'varchar (32)')		AS domainName
	,t.col.value ('details [1]', 'varchar (1024)')			AS details
	,t.col.value ('parentGuid [1]', 'uniqueidentifier')		AS parentGuid
into #x
FROM 
	@xml.nodes ('//row') AS t (col)
Thanks, David

Answer Wiki

Thanks. We'll let you know when a new response is added.

David,
Have you tried using the OPENXML method of doing it?

declare @hDoc INT
 declare @xml xml -- there are 1000 rows in the xml document 
set @xml = /*Your XML Doc here, it didn't copy well.*/

exec sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT *
INTO #x
FROM OPENXML(@hDoc, '//row')
WITH (guid uniqueidentifier './guid/text()',
	rdn varchar(1024) './rdn/text()',
	domainName varchar(32) './domainName/text()',
	details varchar(1024) './details/text()',
	parentGuid uniqueidentifier './parentGuid/text()') as t

exec sp_xml_removedocuemtn @hDoc</

That may give you better results. I’ve processed very large documents using this method without a problem.

It’s also recommended to not use the SELECT INTO command as it caused locking of the system objects in the tempdb and/or local database while the command is running. It’s much better to create the table ahead of time then fill the table via the INSERT INTO command.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following