CODEWITHSUNDEEP
sql-server-2008t-sql
user5245950
user5245950

Reputation:

T-SQL: Display XML data using SELECT statement

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:

enter image description here

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.

Upvotes: 1

Views: 648

Answers (1)

shA.t
shA.t

Reputation: 16958

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);

Upvotes: 1

Related Questions