T-SQL Parse XML Column Problems

Question

This is what the column xml looks like

<Attributes>
    <ProductAttribute ID="9">
        <ProductAttributeValue>
            <Value>21</Value>
        </ProductAttributeValue>
    </ProductAttribute>
    <ProductAttribute ID="10">
        <ProductAttributeValue>
            <Value>25</Value>
        </ProductAttributeValue>
    </ProductAttribute>
</Attributes>

And i want to return it like

ProductAttribute        ProductAttributeValue
--------------------------------------------------
9                       21
10                      25

My query now looks like this

SELECT
    ProductId as ProductId, 
    ProductAttributeNode.value('(@ID)', 'int') as ProductAttributeMappingId,
    ProductAttributeNode.value('(//ProductAttributeValue[1]/Value/text())[1]', 'int') as ProductAttributeValueId
FROM
(
    SELECT
        Id,
        ProductId,
        CAST(AttributesXml as XML) as AttributesXml,
        Sku
    FROM
        ProductAttributeCombination
) AS PAC
CROSS APPLY AttributesXml.nodes('//Attributes/ProductAttribute') as T1(ProductAttributeNode)

And what i get back is

ProductAttribute        ProductAttributeValue
--------------------------------------------------
9                       21
10                      21 <--- not 25

Just some background: // is a deep search* finding each and any node with the given name. The XML will be the whole lot, but - coming out of nodes - with a relative position. That's why you can reach to a parent node with ... The given answer is correct and would work even without the ./. Your <ProductAttributeValue> is right below the current node. — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 17, 2018 at 16:20

Pரதீப் · Accepted Answer · 2018-01-14 15:03:48Z

2

Problem is // in the value method, everytime it looks from the first ProductAttribute element I guess. When you know the parent elements you don't need //

ProductAttributeNode.value('(//ProductAttributeValue[1]/Value/text())[1]', 'int')

should be

ProductAttributeNode.value('(./ProductAttributeValue[1]/Value)[1]', 'int')

edited Jan 14, 2018 at 15:03

answered Jan 14, 2018 at 14:58

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

@Brakke in this case it would be nice to accept the answer.

Collectives™ on Stack Overflow

T-SQL Parse XML Column Problems

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related