Extract value node from xml with multiple xmlns in header

Question

Hi all,
this is my second post on this topic... with a little bit more difficult for me .
I used here below code from Yitzhak Khabinnsky that gave me support in another POST.
Anyway this time in my XML I have 3 xmlns that confuse me very much.

I new to grab the values o this two fields:
<NomeProdotto>Kit Illuminazione intelligente Premium</NomeProdotto>
<CodiceArticolo>SKU-A-00000907</CodiceArticolo>

so I started with a variable...

DECLARE @xml XML =
N'<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/";
xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">;
<env:Header />
<env:Body>
<getConsegna_Ritiro xmlns="http://www.boomi.com/connector/wss">;
<elementRequest>
<Sistema>SFDC</Sistema>
<Processo>CONSEGNA</Processo>
<Causale />
<IdWorkOrder>0WO4I000000QZuQWAW</IdWorkOrder>
<IdCase />
<IdOrdine>a0i4I00000FvecQQAR</IdOrdine>
<ListWorkOrderItem>
<TipologiaProdotto>Smart Home</TipologiaProdotto>
<NomeProdotto>Kit Illuminazione intelligente Premium</NomeProdotto>
<CodiceArticolo>SKU-A-00000907</CodiceArticolo>
<Quantita>1</Quantita>
</ListWorkOrderItem>
</elementRequest>
</getConsegna_Ritiro>
</env:Body>
</env:Envelope>'

SELECT @xml

/*
Which namespace I have to use when like this example I have 3 xmlns in header and 1 in body?
*/

;WITH XMLNAMESPACES (DEFAULT 'http://www.boomi.com/connector/wss')
SELECT c.value('(getConsegna_Ritiro/elementRequest/ListWorkOrderItem/CodiceArticolo/text())[1]','VARCHAR(100)') AS [message]
FROM @xml.nodes('/env') AS t(c);

Answer 1

Please try the following.

SQL

DECLARE @xml XML =
N'<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
              xmlns:xsd="http://www.w3.org/2001/XMLSchema"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <env:Header/>
    <env:Body>
        <getConsegna_Ritiro xmlns="http://www.boomi.com/connector/wss">
            <elementRequest>
                <Sistema>SFDC</Sistema>
                <Processo>CONSEGNA</Processo>
                <Causale/>
                <IdWorkOrder>0WO4I000000QZuQWAW</IdWorkOrder>
                <IdCase/>
                <IdOrdine>a0i4I00000FvecQQAR</IdOrdine>
                <ListWorkOrderItem>
                    <TipologiaProdotto>Smart Home</TipologiaProdotto>
                    <NomeProdotto>Kit Illuminazione intelligente Premium</NomeProdotto>
                    <CodiceArticolo>SKU-A-00000907</CodiceArticolo>
                    <Quantita>1</Quantita>
                </ListWorkOrderItem>
            </elementRequest>
        </getConsegna_Ritiro>
    </env:Body>
</env:Envelope>';

;WITH XMLNAMESPACES (DEFAULT 'http://www.boomi.com/connector/wss'
    , 'http://schemas.xmlsoap.org/soap/envelope/' AS env)
SELECT c.value('(NomeProdotto/text())[1]','VARCHAR(100)') AS NomeProdotto
    , c.value('(CodiceArticolo/text())[1]','VARCHAR(100)') AS CodiceArticolo
FROM @xml.nodes('/env:Envelope/env:Body/getConsegna_Ritiro/elementRequest/ListWorkOrderItem') AS t(c);