INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

XML parse very slow

XML parse very slow

(OP)
I have the following query the runs very slowly. The sample below is just a few XML records. The actual data contains ~11,000 records and takes ~13 minutes to execute. Surely, I'm doing something wrong here. I seek guidance on how to speed up my query. TIA!

DECLARE @xml XML
SELECT @xml = '<output>
<record>
<field name="empid" id="empid" value="23" />
<field name="cbval" id="cbval" value="GD" />
</record>
<record>
<field name="empid" id="empid" value="23" />
<field name="cbval" id="cbval" value="MA" />
</record>
<record>
<field name="empid" id="empid" value="26" />
<field name="cbval" id="cbval" value="RV" />
</record>
<record>
<field name="empid" id="empid" value="78" />
<field name="cbval" id="cbval" value="BR" />
</record>
<record>
<field name="empid" id="empid" value="78" />
<field name="cbval" id="cbval" value="TX" />
</record>
</output>'
;

SELECT x.v.value('(field[@name="empid"]/@value)[1]', 'int'),
x.v.value('(field[@name="cbval"]/@value)[1]', 'varchar(50)')
FROM @xml.nodes('/output/record') x(v)

GO

Dave idea
[http://flashspot.com]

RE: XML parse very slow

I've always found that the "older" method is many times faster than the "newer" method.

Please try this...

CODE

Declare @iDoc Int;
Exec sp_xml_preparedocument @iDoc out, @xml

Select	*
From	OpenXML(@iDoc, '/output/record', 3)
With	(
			EmployeeId int 'field[@name="empid"]/@value[1]',
			cbval varchar(20) 'field[@name="cbval"]/@value[1]'
		)
Exec sp_xml_removedocument @iDoc 

And because I'm curious, can you please post the execution time?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: XML parse very slow

(OP)
I thank you very much for your reply!

I tried this on the full XML and the response is 'Only one top level element is allowed in an XML document.' I Googled that and I did not find anything that clued me in to resolve it. Your script does work on my short XML sample, but does not on the actual XML. The actual XML does display correctly as XML if I "select" it, which suggests to me that the full XML of ~11K records is valid.

So, I'm stymied.

Dave idea
[http://flashspot.com]

RE: XML parse very slow

The One Top Level error is thrown if you have a doc like this :

<output>
<record>
<field name="empid" id="empid" value="23" />
<field name="cbval" id="cbval" value="GD" />
</record>
</output>
<output2>
<record>
<field name="empid" id="empid" value="23" />
<field name="cbval" id="cbval" value="GD" />
</record>
</output2>

It should look like the following :

<output>
<record>
<field name="empid" id="empid" value="23" />
<field name="cbval" id="cbval" value="GD" />
</record>
<record>
<field name="empid" id="empid" value="23" />
<field name="cbval" id="cbval" value="GD" />
</record>
</output>

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006
http://www.greggriffiths.org/livelink/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close