TSQL XML Parsing

Parsing XML using TSQL. Below listed are some approaches.

--Approach I
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number><Message>AAAA</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT ParamValues.ID.query('Number').value('.','integer') as 'Number', ParamValues.ID.query('Message').value('.','VARCHAR(20)') as 'Message'
FROM @x.nodes('/Response/Errors/Error') as ParamValues(ID)

--Approach II
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number><Message>AAAA</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT c.value('./Number[1]', 'Integer')
, c.value('./Message[1]', 'VARCHAR(100)')
FROM @x.nodes('Response/Errors/Error') T(c)

--Approach III
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number><Message>AAAA</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

DECLARE @max INT, @i INT
SELECT @max = @x.query('<e>{ count(/Response/Errors/Error) }</e>').value('e[1]','int')
print @max
SET @i = 1
DECLARE @ErrorId VARCHAR(10)
DECLARE @ErrorMessage VARCHAR(100)
WHILE @i <= @max
BEGIN
SELECT @ErrorId = x.value('Number[1]', 'VARCHAR(10)') FROM @x.nodes('/Response/Errors/Error[position()=sql:variable("@i")]') e(x)
SELECT @ErrorMessage = x.value('Message[1]', 'VARCHAR(100)') FROM @x.nodes('/Response/Errors/Error[position()=sql:variable("@i")]') e(x)
SELECT @ErrorId, @ErrorMessage
SET @i = @i + 1
END

--With SubNodes
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number>
<Message>AAAA</Message>
<SubError>
<SNumber>5</SNumber>
<SMessage>SAAA</SMessage>
</SubError>
</Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT c.value('./Number[1]', 'Integer')
, c.value('./Message[1]', 'VARCHAR(100)')
, c.value('./SubError[1]/SNumber[1]', 'Integer')
, c.value('./SubError[1]/SMessage[1]', 'VARCHAR(100)')
FROM @x.nodes('Response/Errors/Error') T(c)

--Master Status Error 0, Sub Nodes Error Collection

DECLARE @x XML
SET @x = '
<ReturnStatus>
<Status>0</Status>
</ReturnStatus>
<Response>
<Errors>
<Error><Number>1</Number>
<Message>AAAA</Message>
<SubError>
<SNumber>5</SNumber>
<SMessage>SAAA</SMessage>
</SubError>
</Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT
c.value('..[1]/..[1]/..[1]/ReturnStatus[1]/Status[1]', 'Integer')
, c.value('./Number[1]', 'Integer')
, c.value('./Message[1]', 'VARCHAR(100)')
, c.value('./SubError[1]/SNumber[1]', 'Integer')
, c.value('./SubError[1]/SMessage[1]', 'VARCHAR(100)')
FROM @x.nodes('Response/Errors/Error') T(c)

I thought of posting it from my past learning. We can use above approach to load data into a table from XML...

SQL Server 2005 XQuery Performance Tips
Bulk Inserts with XML
OpenXML and XQuery Optimisation Tips
Performance tips of using XML data in SQL Server


Happy Reading!!