CODEWITHSUNDEEP
sql-serverxmlt-sql
Brakke
Brakke

Reputation: 2788

T-SQL Parse XML Column Problems

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

Upvotes: 0

Views: 47

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

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

Upvotes: 2

Related Questions