How to parse a SOAP XML with Header and Lines in SQL Server and show as table?

Question

I want to parse the @xml below and produce a table like:

declare @xml XML = '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <GetInvoiceResponse xmlns="http://www.myCompany.com.au/gateway2/invoicemanagement">
         <GetInvoiceResult>
            <AccountNumber>54321</AccountNumber>
            <InvoiceNumber>Inv10001</InvoiceNumber>
            <Lines>
               <InvoiceLine>
                  <Cost>5.86</Cost>
                  <Ean>Ean111</Ean>
                  <QuantitySupplied>1</QuantitySupplied>
               </InvoiceLine>
               <InvoiceLine>
                  <Cost>4.00</Cost>
                  <Ean>Ean222</Ean>
                  <QuantitySupplied>2</QuantitySupplied>
               </InvoiceLine>
            </Lines>
            <TotalCost>9.86</TotalCost>
         </GetInvoiceResult>
      </GetInvoiceResponse>
   </soap:Body>
</soap:Envelope>';

I want to parse the @xml and produce a table with header and lines.

+---------------+--------+------+-----------+
| InvoiceNumber |  Ean   | Cost | TotalCost |
+---------------+--------+------+-----------+
| Inv10001      | Ean111 | 5.86 |      9.86 |
| Inv10001      | Ean222 | 4.00 |      9.86 |
+---------------+--------+------+-----------+

Answer 1

Like this:

declare @xml XML = '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <GetInvoiceResponse xmlns="http://www.myCompany.com.au/gateway2/invoicemanagement">
         <GetInvoiceResult>
            <AccountNumber>54321</AccountNumber>
            <InvoiceNumber>Inv10001</InvoiceNumber>
            <Lines>
               <InvoiceLine>
                  <Cost>5.86</Cost>
                  <Ean>Ean111</Ean>
                  <QuantitySupplied>1</QuantitySupplied>
               </InvoiceLine>
               <InvoiceLine>
                  <Cost>4.00</Cost>
                  <Ean>Ean222</Ean>
                  <QuantitySupplied>2</QuantitySupplied>
               </InvoiceLine>
            </Lines>
            <TotalCost>9.86</TotalCost>
         </GetInvoiceResult>
      </GetInvoiceResponse>
   </soap:Body>
</soap:Envelope>';

WITH XMLNAMESPACES (DEFAULT 'http://www.myCompany.com.au/gateway2/invoicemanagement')  
select n.value('InvoiceNumber[1]','varchar(15)') InvoiceNumber,
       l.value('Ean[1]','varchar(20)') Ean,
       l.value('Cost[1]','varchar(20)') Cost,
       n.value('TotalCost[1]','decimal(10,2)') TotalCost
from @xml.nodes('//GetInvoiceResult') r(n)
cross apply r.n.nodes('Lines/InvoiceLine') lines(l)

outputs

InvoiceNumber   Ean                  Cost                 TotalCost
--------------- -------------------- -------------------- ---------------------------------------
Inv10001        Ean111               5.86                 9.86
Inv10001        Ean222               4.00                 9.86

It isn't strictly the best form to use the // operator, but this is a small document and it saves you from having to declare the soap namespace and refer to the target node as /soap:Envelope/soap:Body/GetInvoiceResult.