CODEWITHSUNDEEP
sql-serverxmlt-sqlnodes
Yuriy Tigiev
Yuriy Tigiev

Reputation: 187

TSQL XML value return first value of node for all records

I would like using tsql get value of ChargeType from xml file. I wrote script but, it always returns value 'Principal' for both xml records. I can't understand what is wrong and how to fix problem? Script should return values: 

ChargeType
Principal
Taxed

Current result

ChargeType
Principal
Principal

Source code

DECLARE @xml XML = '<ListFinancialEventsResponse xmlns="http://www.test.com">
    <ListFinancialEventsResult>
        <FinancialEvents>
            <ShipmentEventList>
                <ShipmentEvent>
                    <ShipmentItemList>
                        <ShipmentItem>
                            <ItemChargeList>
                                <ChargeComponent>
                                    <ChargeType>Principal</ChargeType>
                                    <ChargeAmount>
                                        <CurrencyAmount>20.4</CurrencyAmount>
                                        <CurrencyCode>RUR</CurrencyCode>
                                    </ChargeAmount>
                                </ChargeComponent>
                                <ChargeComponent>
                                    <ChargeType>Taxed</ChargeType>
                                    <ChargeAmount>
                                        <CurrencyAmount>1.23</CurrencyAmount>
                                        <CurrencyCode>GEL</CurrencyCode>
                                    </ChargeAmount>
                                </ChargeComponent>
                            </ItemChargeList>
                        </ShipmentItem>
                    </ShipmentItemList>
                </ShipmentEvent>
            </ShipmentEventList>
        </FinancialEvents>
    </ListFinancialEventsResult>
</ListFinancialEventsResponse>';


;WITH XMLNAMESPACES('http://www.test.com' as ns)
select 
        lfer.c.value('(//ns:ChargeType)[1]', 'nvarchar(50)') AS ChargeType
from @xml.nodes('//ns:ListFinancialEventsResponse//ns:ListFinancialEventsResult//ns:ShipmentItemList//ns:ShipmentItem//ns:ItemChargeList//ns:ChargeComponent') lfer(c)

Upvotes: 2

Views: 440

Answers (1)

marc_s
marc_s

Reputation: 754538

Well, either you need to specify the whole list of nodes above <ChargeType> in your XPath, using single dashes (right now, you're leaving out a few)

@xml.nodes('/ns:ListFinancialEventsResponse/ns:ListFinancialEventsResult/ns:FinancialEvents/ns:ShipmentEventList .......

or then you need to use this XPath to get just the <ChargeComponent> nodes and grab the <ChargeType> from those.

Try this T-SQL:

;WITH XMLNAMESPACES('http://www.test.com' as ns)
SELECT 
    -- do *NOT* use double dashes here!
    lfer.c.value('(ns:ChargeType)[1]', 'nvarchar(50)') AS ChargeType
FROM
    -- just grab *all* <ChargeComponent> nodes anywhere in the XML
    @xml.nodes('//ns:ChargeComponent') lfer(c)

Your existing code here:

lfer.c.value('(//ns:ChargeType)[1]'

means: give me all the <ChargeType> nodes (because of the leading //) and then take the first of all of those nodes - that's why you're getting the node with the Principal twice

Upvotes: 5

Related Questions