T-SQL: Display XML data using SELECT statement

Question

I have the following XML text:

 <Elements>
    <imported>
        <product name="Software #">0 </product>
        <product name="Hardware">1000 Pieces  </product>
        <product name="Parts">300 </product>
        <product name="Wholes sales">1000</product>
        <product name="Cars">Audi (10) Porche (22) Skoda (48)</product>
        <product name="Final Report">0</product>
    </imported>
</Elements>

This XML data is stored in a nvarchar(max) column, and it looks like this:

I want to do a select statement (or store the info in a temp table) and display a more readable form of that XML data and extract the values of the XML nodes, something like:

Column1  | Column2
------------------
Software | 0
Hardware | 1000 pieces

I am using SQL Server 2008.

If it looks like XML, smells like XML, behaves like XML - ***why*** isn't it **stored** as `XML` ?? That would be the most sensible thing to do, and then use XQuery functions on it. Those don't work on `nvarchar(max)`..... — marc_s, Jan 29 '16 at 06:07
Possible duplicate: http://stackoverflow.com/questions/16838627/import-xml-into-sql-server — Deep Kalra, Jan 29 '16 at 06:26

score 1 · Accepted Answer · answered Jan 29 '16 at 06:44

I think you can use a query after converting your string to xml like this:

DECLARE @x xml = @xml;

SELECT 
    c.value('@name', 'nvarchar(MAX)') AS Column1,
    c.value('.[1]', 'nvarchar(MAX)') AS Column2

FROM 
    @x.nodes('/Elements/imported/product') As t(c);

Or an inline convert like this:

SELECT 
    c.value('@name', 'nvarchar(MAX)') AS Column1,
    c.value('.[1]', 'nvarchar(MAX)') AS Column2

FROM 
    (SELECT CAST(@xml as xml) x) dt CROSS APPLY
    dt.x.nodes('/Elements/imported/product') As t(c);

T-SQL: Display XML data using SELECT statement

1 Answers1