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