SQL Server - Query The Data From XML File

Question

I am new to XML stuff. I've figured out how to query and return the values from the XML file (example below). However, I run into a problem that it only capture the first node of 'SerialNo' tag because the tag has the same node name "SerialNo" repeated. In the XML file, it has 4 serial numbers for SKU#TT234343, but it only gives me the first Serial11111. I am totally stuck and don't know how to list all of those serial#.

I would like the query result for SKU#TT234343, listing all 4 serial numbers if possible.

Please help. Thanks!

The XML File looks like:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<ROOT>
  <ShipNotice version="1" >
    <InvoiceDate>01/01/2015</InvoiceDate>
    <InvoiceNumber>6868686</InvoiceNumber>
    <ShipDate>02/02/2015</ShipDate>
    <ShipTime>2306</ShipTime>
    <PONumber>P444444</PONumber>
    <PODate>03/03/2015</PODate>
    <ShipCode>XXX</ShipCode>
    <ShipDescription>FedEx Economy</ShipDescription>
    <ShipTo>
      <AddressName>ShipABC</AddressName>
      <AddressContact>Name1</AddressContact>
      <AddressLine1>2222 Street Name</AddressLine1>
      <AddressLine2> </AddressLine2>
      <City>AUSTIN</City>
      <State>TX</State>
      <ZipCode>78111</ZipCode>
    </ShipTo>
    <BillTo>
      <AddressName>BillABC</AddressName>
      <AddressContact>Name1</AddressContact>
      <AddressLine1>1234 Street Name</AddressLine1>
      <AddressLine2>-SUITE 111</AddressLine2>
      <City>Los Angeles</City>
      <State>CA</State>
      <ZipCode>95136</ZipCode>
    </BillTo>
    <TotalWeight>324</TotalWeight>
    <EmptyCartonWGT>0</EmptyCartonWGT>
    <NumberOfCarton>1</NumberOfCarton>
    <DirectShipFlag>D</DirectShipFlag>
    <ShipFromWarehouse>88</ShipFromWarehouse>
    <ShipFromZip>94538</ShipFromZip>
    <ShipTrackNo>33333333</ShipTrackNo>
    <EndUserPONumber>55555555</EndUserPONumber>
    <CustomerSONumber/>
    <Package sequence="1" >
      <TrackNumber>666666666</TrackNumber>
      <PackageWeight>324</PackageWeight>
      <Item sequence="1" >
        <SOLineNo>1</SOLineNo>
        <MfgPN>XYZ1111111</MfgPN>
        <SKU>TT234343</SKU>
        <ShipQuantity>4</ShipQuantity>
        <CustPOLineNo>1</CustPOLineNo>
        <CustSOLineNo/>
        <Description>Server1234</Description>
        <CustPN/>
        <UPC/>
        <UnitPrice>1000</UnitPrice>
        <EndUserPOLineNo>0</EndUserPOLineNo>
        <SerialNo>Serial11111</SerialNo>
        <SerialNo>Serial22222</SerialNo>
        <SerialNo>Serial33333</SerialNo>
        <SerialNo>Serial44444</SerialNo>
      </Item>
      <Item sequence="2" >
        <SOLineNo>2</SOLineNo>
        <MfgPN>XYZ222222</MfgPN>
        <SKU>TT8848788</SKU>
        <ShipQuantity>4</ShipQuantity>
        <CustPOLineNo>2</CustPOLineNo>
        <CustSOLineNo/>
        <Description>GGG localization</Description>
        <CustPN/>
        <UPC/>
        <UnitPrice>0.00</UnitPrice>
        <EndUserPOLineNo>0</EndUserPOLineNo>
        <SerialNo/>
      </Item>
    </Package>
  </ShipNotice>
</ROOT>

The SQL Query:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlData

SELECT 
InvoiceNumber, PONumber, PODate
, AddressName
, MfgPN, SerialNo
--, AddressContact, AddressLine1, AddressLine2, City, State, ZipCode
FROM OPENXML(@hDoc, '/ROOT/ShipNotice/Package/Item')
WITH 
(
    --- #################  Level 1 #################
    InvoiceNumber [varchar](50) '../../InvoiceNumber',
    PONumber [varchar](100) '../../PONumber',
    PODate [varchar](100) '../../PODate',

    --- #################  Level 2 #################
    AddressName [varchar](100) '../../ShipTo/AddressName',


    --- #################  Level 3 #################
    MfgPN [varchar](100) 'MfgPN',
    SerialNo [varchar](100) 'SerialNo'

)

Answer 1

You can try using the newer technology XQuery instead of OPENXML(). Using XQuery, you can use nodes() method to shred the XML on elements that will correspond to the rows in the output, and use value() to extract the element value :

SELECT
    shipnotice.value('InvoiceNumber[1]','varchar(20)') InvoiceNumber
    , shipnotice.value('PONumber[1]','varchar(20)') PONumber
    , shipnotice.value('PODate[1]','varchar(20)') PODate
    , shipnotice.value('(ShipTo/AddressName)[1]','varchar(100)') AddressName
    , item.value('MfgPN[1]','varchar(100)') MfgPN
    , serialno.value('.','varchar(100)') SerialNo
FROM @XML.nodes('/ROOT/ShipNotice') as t(shipnotice)
    OUTER APPLY shipnotice.nodes('Package/Item') as t2(item)
    OUTER APPLY item.nodes('SerialNo') as t3(serialno)

Sqlfiddle Demo

output :

| InvoiceNumber | PONumber |     PODate | AddressName |      MfgPN |    SerialNo |
|---------------|----------|------------|-------------|------------|-------------|
|       6868686 |  P444444 | 03/03/2015 |     ShipABC | XYZ1111111 | Serial11111 |
|       6868686 |  P444444 | 03/03/2015 |     ShipABC | XYZ1111111 | Serial22222 |
|       6868686 |  P444444 | 03/03/2015 |     ShipABC | XYZ1111111 | Serial33333 |
|       6868686 |  P444444 | 03/03/2015 |     ShipABC | XYZ1111111 | Serial44444 |
|       6868686 |  P444444 | 03/03/2015 |     ShipABC |  XYZ222222 |             |