XML Parsing IN SQL Server

COOL SQLServer code of the week
======================================


Hey Integration Fans....

Cool Code of the week.... so using Pervasive I was trying to parse an XML file like the Sample below. It had 125000 Request segments and was taking 45 minutes to process. I changed to delimited text source like I had done at FirstCare, and did my own parsing and that brought the time down by 10%.

The I tried the SQL code below to parse the XML inside of SQL SERVER... and that brought the parsing down to 125 seconds in 2 passes ( one for each request type). Note the savings was from 2700 seconds to 125, a reduction to 4.6% of the original time.

Enjoy this could be really helpful.

David


Sample XML showing schema....

<ETLWebServiceRequests>
<Request RequestType="CONSUMERTAG">
<assign-consumer-tag>
<tag-short-name>RpLexNex</tag-short-name>
<consumer-agency-identifier>1379376</consumer-agency-identifier>
</assign-consumer-tag>
</Request>
<Request RequestType="AREVENT">
<save-arevent-with-shortnames>
<consumer-agency-identifier>1601904</consumer-agency-identifier>
<action-code-shortname>CNSMSCR</action-code-shortname>
<result-code-shortname>SKPINFO</result-code-shortname>
<message-text>Received New score information from Skiptrace Lexis Nexis</message-text>
</save-arevent-with-shortnames>
</Request>
</ETLWebServiceRequests>


And here is the SQL:
----------------------------------

DECLARE @XML XML;
SELECT @XML = CAST((SELECT * FROM OPENROWSET (BULK '\\wasvpdb005\FileImport\LN_PostProcess\LN_Tag.xml' , SINGLE_BLOB) AS x) AS XML);
SELECT RequestNodes,
CAST( RequestNodes AS NVARCHAR(4000)) RequestNodeTxt,
(
SELECT T.c.value('.','varchar(8)')
FROM RequestNodes.nodes('/assign-consumer-tag/tag-short-name[1]') T(c)
) tag_short_name,
(
SELECT T.c.value('.','int')
FROM RequestNodes.nodes('/assign-consumer-tag/consumer-agency-identifier[1]') T(c)
) consumer_agency_identifier
FROM
(
SELECT T.c.query('.') AS RequestNodes
--, T.c.value('../@RequestType','varchar(50)') AS result
FROM @XML.nodes('/ETLWebServiceRequests/Request/*') T(c)
) RequestNodes

SELECT RequestNodes,
CAST( RequestNodes AS NVARCHAR(4000)) RequestNodeTxt,
(
SELECT T.c.value('.','varchar(8)')
FROM RequestNodes.nodes('/save-arevent-with-shortnames/action-code-shortname[1]') T(c)
) action_code_shortname,
(
SELECT T.c.value('.','varchar(8)')
FROM RequestNodes.nodes('/save-arevent-with-shortnames/message-text[1]') T(c)
) message_text,
(
SELECT T.c.value('.','varchar(8)')
FROM RequestNodes.nodes('/save-arevent-with-shortnames/action-code-shortname[1]') T(c)
) action_code_shortname,
(
SELECT T.c.value('.','int')
FROM RequestNodes.nodes('/save-arevent-with-shortnames/consumer-agency-identifier[1]') T(c)
) consumer_agency_identifier
FROM
(
SELECT T.c.query('.') AS RequestNodes
--, T.c.value('../@RequestType','varchar(50)') AS result
FROM @XML.nodes('/ETLWebServiceRequests/Request/*') T(c)
) RequestNodes collapse