Wednesday, 10 December 2014

Unable to pull in more than one row when parsing XML data into SQL



I have seen a few XML parsing examples, and tried several but I am stuck on how to display all rows


My H:\Sample.xml file looks like:



<?xml version="1.0" encoding="utf-8"?>
<Level1>
<Level2>
<Level3>
<Data>Line1</Data>
<Data>Line2</Data>
<Data>Line3</Data>
<Data>Line4</Data>
</Level3>
</Level2>
</Level1>


My Levels don't have values attached, and I just want to produce a column of the 4 data lines.


I have tried three methods:


First Method: Using OpenRowSet and .nodes



DECLARE @Data XML

SET @Data = ( SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'H:\Sample.xml', SINGLE_BLOB) as X )
SELECT
a.b.value('Data[1]','nvarchar(255)') as Detail
FROM @Data.nodes('Level1/Level2/Level3')a(b)


Second Method: Using Cross Apply



WITH xmlFile (Contents) AS (
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'H:\Sample.xml', SINGLE_BLOB) as XmlData
)
SELECT
c.value('(Data)[1]', 'varchar(255)') AS Detail
FROM XmlFile CROSS APPLY Contents.nodes ('(//Level1/Level2/Level3)') AS t(c);


Third Method: Using sp_xml_preparedocument



DECLARE @hDoc AS INT
DECLARE @SQL NVARCHAR (MAX)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @Data

SELECT Detail
FROM OPENXML(@hDoc, 'Level1/Level2/Level3')
WITH (Detail [varchar](255) '@Data')

EXEC sp_xml_removedocument @hDoc
GO


The first two examples show "Line1" and the last one shows NULL (all just one line each)


No comments:

Post a Comment