How do I query values ​from XML nodes using the XML query function

Question

I have the following XML data:

DECLARE @x XML   
SET @x='<Orders>
<HeadingSection>
    <DocumentNameCode>Order</DocumentNameCode>
    <DetailSection>
        <LineItem LineItemNumber="10">
            <ItemId>123456789</ItemId>
            <ItemTypeId>EN</ItemTypeId>
            <AdditionalProductId Qualifier="ProductId">
                <Item>
                <ItemId>ABC</ItemId>
                <TypeId>SA</TypeId>
                </Item>
            </AdditionalProductId>
            <AdditionalProductId Qualifier="ProductId">
                <Item>
                <ItemId>XYZ</ItemId>
                <TypeId>IN</TypeId>
                </Item>
            </AdditionalProductId>
        </LineItem>
    </DetailSection>
    <SummarySection/>
</HeadingSection>
</Orders>'

I already use the following T-SQL script (must have a cursor)

DECLARE @xmlQuittungPOS XML
DECLARE quittungCurPOS CURSOR FOR
    SELECT QuittungXMLPOS = T.X.query('.')         
    FROM @x.nodes('/Orders/HeadingSection/DetailSection/LineItem') AS T(X)  
    
OPEN quittungCurPOS
    
FETCH NEXT FROM quittungCurPOS INTO @xmlQuittungPOS
    
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO dbo.tempPOS (

GTIN
,edi_PIA_SA
,edi_PIA_IN

)


SELECT

GTIN = T.X.query('/LineItem[ItemTypeId = "BP"]').value('(/LineItem/ItemId)[1]','VARCHAR(100)') -- funktioniert / works

,edi_PIA_SA = T.X.query('/LineItem/AdditionalProductId/Item[TypeId = "SA"]').value('(/LineItem/AdditionalProductId/Item/ItemId)[1]','VARCHAR(255)') -- funktioniert nicht / doesn't work

,edi_PIA_IN = T.X.query('/LineItem/AdditionalProductId/Item[TypeId = "IN"]').value('(/LineItem/AdditionalProductId/Item/ItemId)[1]','VARCHAR(255)') -- funktioniert nicht / doesn't work

FROM 
@xmlQuittungPOS.nodes('/LineItem') AS T(X)
    
FETCH NEXT FROM quittungCurPOS INTO @xmlQuittungPOS
END

CLOSE quittungCurPOS
DEALLOCATE quittungCurPOS

1. GTIN -> query works
2. PIA_SA and PIA_IN -> query doesn't work

Question:

I think it's because /LineItem/AdditionalProductId/Item[TypeId = "SA"] are in additional nodes.

Question:

How do I get the values of TypeId "SA" and "IN" ​​in

<AdditionalProductId Qualifier="ProductId">
<Item>
<ItemId>**ABC**</ItemId>
<TypeId>SA</TypeId>
</Item>
</AdditionalProductId>

<AdditionalProductId Qualifier="ProductId">
<Item>
<ItemId>**XYZ**</ItemId>
<TypeId>IN</TypeId>
</Item>
</AdditionalProductId>

with .value('(/LineItem/AdditionalProductId/Item/ItemId)[1]','VARCHAR(255)')

! at Level 1 !

I know.

SA on Level 1 -> .value('(/LineItem/AdditionalProductId/Item/ItemId)[1]','VARCHAR(255)') SA on Level 2 -> .value('(/LineItem/AdditionalProductId/Item/ItemId)[2]','VARCHAR(255)')

but I now need the query for the type

Thanks

Answer 1

It's unclear what you are after here, so I'm answering for both. If you want 1 row per Item node, then you just need to use nodes to the Item node and get the value of ItemId text:

DECLARE @xml xml = '<AdditionalProductId Qualifier="ProductId">
<Item>
<ItemId>**ABC**</ItemId>
<TypeId>SA</TypeId>
</Item>
</AdditionalProductId>

<AdditionalProductId Qualifier="ProductId">
<Item>
<ItemId>**XYZ**</ItemId>
<TypeId>IN</TypeId>
</Item>
</AdditionalProductId>
';

SELECT AP.I.value('(ItemId/text())[1]','varchar(15)') AS ItemId,
       AP.I.value('(TypeId/text())[1]','varchar(15)') AS TypeId
FROM (VALUES(@XML))V(X) --To give impression it's against a table
      CROSS APPLY V.X.nodes ('AdditionalProductId/Item')AP(I);

if, however, you want 1 column per different value of TypeID you could either filter in the XML:

SELECT X.AP.value('(AdditionalProductId/Item[TypeId[(text()[1])="SA"]]/ItemId/text())[1]','varchar(15)') AS ItemSA,
       X.AP.value('(AdditionalProductId/Item[TypeId[(text()[1])="IN"]]/ItemId/text())[1]','varchar(15)') AS ItemIN
FROM @xml.nodes ('/')X(AP); --To give impression it's against a table

Or use the prior query and use conditional aggregation:

WITH Items AS(
    SELECT AP.I.value('(ItemId/text())[1]','varchar(15)') AS ItemId,
           AP.I.value('(TypeId/text())[1]','varchar(15)') AS TypeId
    FROM @xml.nodes ('AdditionalProductId/Item')AP(I))
SELECT MAX(CASE TypeID WHEN 'SA' THEN ItemId END) AS ItemSA,
       MAX(CASE TypeID WHEN 'IN' THEN ItemId END) AS ItemIN
FROM Items;

Answer 2

Thanks for the answers and I have found my solution for now.

value('(AdditionalProductId/Item[TypeId[(text()[1])="SA"]]/ItemId/text())[1]

I would be happy to see the other tips