My XML procedure is so slo-o-o-o-w

Question

Post reply

My XML procedure is so slo-o-o-o-w

rightfield

SSCrazy

Points: 2418
More actions
July 6, 2010 at 10:34 am

#220845

I have the procedure below reading an XML file, then manipulating it to get the two columns I want, SSN and the first level 'Taxable Wages' (not the out-of-state). (I also get an extra column but I am not interested in that, I am only interested in getting the SSN and the in-State Taxable Wages from the XML.) Problem is that it is so very slow. I have put a set of sample data (16 employees) after the procedure if needed. The function I use in my procedure, fn_XMLTable, was posted by Jacob Sebastian and can be found at http://beyondrelational.com/blogs/jacob/archive/2010/05/30/select-from-xml.aspx
Thank you for your suggestions on how to make this procedure faster.
/*Start of procedure*/
alter Procedure pes_xmlReader
(
@xmlFileName VARCHAR(500) = '\\Sql_prod_s01\d$\TEMP\FL04Q2_v4.xml'
)
AS
BEGIN
--CREATE TABLE XmlImportTest (xmlFileName VARCHAR(300), xml_data xml)
TRUNCATE TABLE XmlImportTest
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
declare @xmlstring varchar(max)
set @xmlstring = (select convert(varchar(max),xml_data) from XmlImportTest);
--SELECT * FROM dbo.fn_XMLTable(@xmlstring);
WITH xml_CTE (XPathIndex, NodeName, NodeValue)
AS
(
SELECT left(XPath,charindex(']',XPath,8)) as XPathIndex, NodeName, [Value] as NodeValue
FROM dbo.fn_XMLTable(@xmlstring) where NodeName='TaxableWages' AND Depth=3
UNION ALL
SELECT left(XPath,charindex(']',XPath,8)) as XPathIndex, NodeName, [Value] as NodeValue
FROM dbo.fn_XMLTable(@xmlstring) where NodeName='SSN'
)
--select * from xml_CTE
select A.XPathIndex, A.NodeValue, B.NodeValue
from xml_CTE as A join xml_CTE as B on A.XPathIndex=B.XPathIndex and A.NodeName!=B.NodeName where A.NodeName = 'SSN'
END
/*End of procedure*/
/*Sample XML data*/
<ROOT>
<Employee>
<SSN>123456789</SSN>
<Employee>
<FirstName>DOREEN </FirstName>
<MiddleName>B</MiddleName>
<LastName>jukyuk </LastName>
<Suffix/>
</Employee>
<TotalWages>10244.28</TotalWages>
<TaxableWages>0.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>234567891</SSN>
<Employee>
<FirstName>ANDRE </FirstName>
<MiddleName>R</MiddleName>
<LastName>uykyujyj </LastName>
<Suffix/>
</Employee>
<TotalWages>20377.49</TotalWages>
<TaxableWages>1000.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>345678912</SSN>
<Employee>
<FirstName>Michael </FirstName>
<MiddleName>J</MiddleName>
<LastName>srdgretr5e </LastName>
<Suffix/>
</Employee>
<TotalWages>549.00</TotalWages>
<TaxableWages>549.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>456789123</SSN>
<Employee>
<FirstName>MATTHEW </FirstName>
<MiddleName>J</MiddleName>
<LastName>ertesrgfes </LastName>
<Suffix/>
</Employee>
<TotalWages>1760.00</TotalWages>
<TaxableWages>1760.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>567891234</SSN>
<Employee>
<FirstName>DEBORAH </FirstName>
<MiddleName>L</MiddleName>
<LastName>sertesrtesr </LastName>
<Suffix/>
</Employee>
<TotalWages>5170.31</TotalWages>
<TaxableWages>5170.31</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>678912345</SSN>
<Employee>
<FirstName>COREY </FirstName>
<MiddleName>R</MiddleName>
<LastName>srtertsettt </LastName>
<Suffix/>
</Employee>
<TotalWages>238.00</TotalWages>
<TaxableWages>238.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>789123456</SSN>
<Employee>
<FirstName>JADE </FirstName>
<LastName>ertestert </LastName>
<Suffix/>
</Employee>
<TotalWages>1509.82</TotalWages>
<TaxableWages>1509.82</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>891234567</SSN>
<Employee>
<FirstName>MICHAEL </FirstName>
<MiddleName>L</MiddleName>
<LastName>ertertert </LastName>
<Suffix/>
</Employee>
<TotalWages>6232.92</TotalWages>
<TaxableWages>0.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>912345678</SSN>
<Employee>
<FirstName>DOREEN </FirstName>
<MiddleName>B</MiddleName>
<LastName>erstserrrrrrr </LastName>
<Suffix/>
</Employee>
<TotalWages>10244.28</TotalWages>
<TaxableWages>0.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>112345678</SSN>
<Employee>
<FirstName>ANDRE </FirstName>
<MiddleName>R</MiddleName>
<LastName>rdtrettttttttt </LastName>
<Suffix/>
</Employee>
<TotalWages>20377.49</TotalWages>
<TaxableWages>1000.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>223456789</SSN>
<Employee>
<FirstName>Michael </FirstName>
<MiddleName>J</MiddleName>
<LastName>rdgrsetert </LastName>
<Suffix/>
</Employee>
<TotalWages>549.00</TotalWages>
<TaxableWages>549.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>334567891</SSN>
<Employee>
<FirstName>MATTHEW </FirstName>
<MiddleName>J</MiddleName>
<LastName>ergsertete </LastName>
<Suffix/>
</Employee>
<TotalWages>1760.00</TotalWages>
<TaxableWages>1760.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>445678912</SSN>
<Employee>
<FirstName>DEBORAH </FirstName>
<MiddleName>L</MiddleName>
<LastName>fgrgre </LastName>
<Suffix/>
</Employee>
<TotalWages>5170.31</TotalWages>
<TaxableWages>5170.31</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>556789123</SSN>
<Employee>
<FirstName>COREY </FirstName>
<MiddleName>R</MiddleName>
<LastName>fbsrggser </LastName>
<Suffix/>
</Employee>
<TotalWages>238.00</TotalWages>
<TaxableWages>238.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>667891234</SSN>
<Employee>
<FirstName>JADE </FirstName>
<LastName>erterg </LastName>
<Suffix/>
</Employee>
<TotalWages>1509.82</TotalWages>
<TaxableWages>1509.82</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>778912345</SSN>
<Employee>
<FirstName>MICHAEL </FirstName>
<MiddleName>L</MiddleName>
<LastName>dfhtrhh </LastName>
<Suffix/>
</Employee>
<TotalWages>6232.92</TotalWages>
<TaxableWages>0.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
</ROOT>

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why not simply using the "old-fashioned" method?

SELECT

xmlFileName,

c.value('SSN[1]', 'varchar(30)') AS ssn,

c.value('TaxableWages[1]' , 'varchar(30)') AS TaxableWages

FROM XmlImportTest

CROSS APPLY

xml_data.nodes('ROOT/Employee') T(c)

If this solution doesn't give you the desired result you might want to post your expected data so we have something to compare against...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

DECLARE@data XML = '

<ROOT>

<Employee>

<SSN>123456789</SSN>

<Employee>

<FirstName>DOREEN </FirstName>

<MiddleName>B</MiddleName>

<LastName>jukyuk </LastName>

<Suffix/>

</Employee>

<TotalWages>10244.28</TotalWages>

<TaxableWages>0.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>234567891</SSN>

<Employee>

<FirstName>ANDRE </FirstName>

<MiddleName>R</MiddleName>

<LastName>uykyujyj </LastName>

<Suffix/>

</Employee>

<TotalWages>20377.49</TotalWages>

<TaxableWages>1000.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>345678912</SSN>

<Employee>

<FirstName>Michael </FirstName>

<MiddleName>J</MiddleName>

<LastName>srdgretr5e </LastName>

<Suffix/>

</Employee>

<TotalWages>549.00</TotalWages>

<TaxableWages>549.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>456789123</SSN>

<Employee>

<FirstName>MATTHEW </FirstName>

<MiddleName>J</MiddleName>

<LastName>ertesrgfes </LastName>

<Suffix/>

</Employee>

<TotalWages>1760.00</TotalWages>

<TaxableWages>1760.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>567891234</SSN>

<Employee>

<FirstName>DEBORAH </FirstName>

<MiddleName>L</MiddleName>

<LastName>sertesrtesr </LastName>

<Suffix/>

</Employee>

<TotalWages>5170.31</TotalWages>

<TaxableWages>5170.31</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>678912345</SSN>

<Employee>

<FirstName>COREY </FirstName>

<MiddleName>R</MiddleName>

<LastName>srtertsettt </LastName>

<Suffix/>

</Employee>

<TotalWages>238.00</TotalWages>

<TaxableWages>238.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>789123456</SSN>

<Employee>

<FirstName>JADE </FirstName>

<LastName>ertestert </LastName>

<Suffix/>

</Employee>

<TotalWages>1509.82</TotalWages>

<TaxableWages>1509.82</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>891234567</SSN>

<Employee>

<FirstName>MICHAEL </FirstName>

<MiddleName>L</MiddleName>

<LastName>ertertert </LastName>

<Suffix/>

</Employee>

<TotalWages>6232.92</TotalWages>

<TaxableWages>0.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>912345678</SSN>

<Employee>

<FirstName>DOREEN </FirstName>

<MiddleName>B</MiddleName>

<LastName>erstserrrrrrr </LastName>

<Suffix/>

</Employee>

<TotalWages>10244.28</TotalWages>

<TaxableWages>0.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>112345678</SSN>

<Employee>

<FirstName>ANDRE </FirstName>

<MiddleName>R</MiddleName>

<LastName>rdtrettttttttt </LastName>

<Suffix/>

</Employee>

<TotalWages>20377.49</TotalWages>

<TaxableWages>1000.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>223456789</SSN>

<Employee>

<FirstName>Michael </FirstName>

<MiddleName>J</MiddleName>

<LastName>rdgrsetert </LastName>

<Suffix/>

</Employee>

<TotalWages>549.00</TotalWages>

<TaxableWages>549.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>334567891</SSN>

<Employee>

<FirstName>MATTHEW </FirstName>

<MiddleName>J</MiddleName>

<LastName>ergsertete </LastName>

<Suffix/>

</Employee>

<TotalWages>1760.00</TotalWages>

<TaxableWages>1760.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>445678912</SSN>

<Employee>

<FirstName>DEBORAH </FirstName>

<MiddleName>L</MiddleName>

<LastName>fgrgre </LastName>

<Suffix/>

</Employee>

<TotalWages>5170.31</TotalWages>

<TaxableWages>5170.31</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>556789123</SSN>

<Employee>

<FirstName>COREY </FirstName>

<MiddleName>R</MiddleName>

<LastName>fbsrggser </LastName>

<Suffix/>

</Employee>

<TotalWages>238.00</TotalWages>

<TaxableWages>238.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>667891234</SSN>

<Employee>

<FirstName>JADE </FirstName>

<LastName>erterg </LastName>

<Suffix/>

</Employee>

<TotalWages>1509.82</TotalWages>

<TaxableWages>1509.82</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

<Employee>

<SSN>778912345</SSN>

<Employee>

<FirstName>MICHAEL </FirstName>

<MiddleName>L</MiddleName>

<LastName>dfhtrhh </LastName>

<Suffix/>

</Employee>

<TotalWages>6232.92</TotalWages>

<TaxableWages>0.00</TaxableWages>

<OutOfStateWages>

<TotalWages> 0.00</TotalWages>

<TaxableWages> 0.00</TaxableWages>

<TaxState>XX</TaxState>

</OutOfStateWages>

</Employee>

</ROOT>'

SELECTn.value('../../SSN[1]', 'VARCHAR(MAX)') AS SSN,

n.value('../../TotalWages[1]', 'MONEY') AS TotalWages,

n.value('../../TaxableWages[1]', 'MONEY') AS TaxableWages,

n.value('local-name(..)', 'VARCHAR(MAX)') AS ParentName,

n.value('local-name(.)', 'VARCHAR(MAX)') AS ElementName,

n.value('.', 'VARCHAR(MAX)') AS ElementValue

FROM@data.nodes('/*/*/*/*') AS r(n)

N 56°04'39.16"
E 12°55'05.25"

rightfield SSCrazy Points: 2418 More actions · Answer 3

Thank you both. I have never done anything with XML in TSQL. With these working solutions I will be able to learn a bit more. Thank you.