Parse XML in SQL Server, Return in Table Format

Question

The XML looks like this:

<Attributes>
<ProductAttribute ID="92">
    <ProductAttributeValue>
        <Value>225</Value>
    </ProductAttributeValue>
</ProductAttribute>
<ProductAttribute ID="536">
    <ProductAttributeValue>
        <Value>227</Value>
    </ProductAttributeValue>
</ProductAttribute>

I need

ID  - Value
92  - 225
536 - 227

In Table Format.

Is it possible to get this values directly from a query or stored procedure or Function?

MS Docs has an article on XML data and SQL Server, which you might find useful. — David Rushton
– David Rushton, Commented May 23, 2017 at 10:13
Did you check the docs? They have an entire chapter on storing and parsing XML, queries, transformations etc. Do you have a specific problem? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 23, 2017 at 10:26
I have checked it but unable to buildup query same as i want result in table structure. — Hiteshkumar Vaghasiya
– Hiteshkumar Vaghasiya, Commented May 23, 2017 at 10:27

TriV · Accepted Answer · 2017-05-23 10:40:05Z

1

You could use value() and nodes method for xml

DECLARE @xml XML = N'<Attributes>
<ProductAttribute ID="92">
    <ProductAttributeValue>
        <Value>225</Value>
    </ProductAttributeValue>
</ProductAttribute>
<ProductAttribute ID="536">
    <ProductAttributeValue>
        <Value>227</Value>
    </ProductAttributeValue>
</ProductAttribute>
</Attributes>'

SELECT ID = x.t.value('(./@ID)[1]','int'),
       Value = x.t.value('(./ProductAttributeValue/Value)[1]','int') 
FROM @xml.nodes('/Attributes/ProductAttribute') AS x(t)

Returns

ID  Value
---------
92  225
536 227

answered May 23, 2017 at 10:40

TriV

5,1582 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse XML in SQL Server, Return in Table Format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related