XML parse very slow

[ElEye]

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
[

http://flashspot.com

]

 

[gmmastros]

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

Please try this...

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

 

[ElEye]

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
[

http://flashspot.com

]

 

[ggriffit]

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/