1

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.

Improve this question
2
  • 1
    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)..... Commented Jan 29, 2016 at 6:07
  • 1
    Possible duplicate: stackoverflow.com/questions/16838627/import-xml-into-sql-server Commented Jan 29, 2016 at 6:26

1 Answer 1

Reset to default
1

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.