Can someone please help... I cannot seem to figure this out... I need to parse this using T-SQL. I have it saved as a file in the C:\Temp directory, named "OpenPos.XML". Any help would be very greatly appreciated. I need to be able to parse the XML and write it to a SQL table.

If I do an outer apply at all levels, it applies every PO # to every line instead of just for the lines related to that PO #.

XML File:

<?xml version="1.0" encoding="utf-8"?>
<acXML lang="en-us" xmlns="https://www.autocrib.net/acXMLSchema.xsd">
  <Header>
    <From>
      <Company>Rolls Royce ATC (Indianapolis)</Company>
      <Identity>rollsroyceatc-indianapolis</Identity>
      <DatabaseName>AutoCribNet2</DatabaseName>
    </From>
  </Header>
  <Request>
    <OpenPurchaseOrderRequest ReqType="GET">
      <PoNo>1716W</PoNo>
      <ExternalPoNo />
      <LineItems>
        <Item>
          <Line>1</Line>
          <TagNo>1716</TagNo>
          <VendorID>QMS</VendorID>
          <ItemID>CARD BC6266</ItemID>
          <ItemType>Expendable</ItemType>
          <ItemRFID />
          <Station>01</Station>
          <Bin>0825-04-B-06</Bin>
          <OrderQty>1</OrderQty>
          <Received>1</Received>
          <ReceivedBurn>0</ReceivedBurn>
          <PackQty>1</PackQty>
          <UnitCost>0.0000</UnitCost>
          <UnitPrice>0.0000</UnitPrice>
          <Lot />
          <IsSpotBuy>False</IsSpotBuy>
          <SpotTranCode>0</SpotTranCode>
          <Inspect>False</Inspect>
          <InspDate />
          <InspOnHand>0</InspOnHand>
          <InspBurn>0</InspBurn>
          <OrderDate>01-13-2022</OrderDate>
          <DueDate>01-25-2022</DueDate>
          <PromiseDt>01-25-2022</PromiseDt>
          <ReceiveDt />
          <Department />
          <Job />
          <Machine />
          <Reason />
          <Part />
          <Processed>False</Processed>
        </Item>
      </LineItems>
    </OpenPurchaseOrderRequest>
    <OpenPurchaseOrderRequest ReqType="GET">

SQL: 
--Open POs
SELECT PO_X = CAST(BulkColumn AS xml)
INTO #PO
FROM OPENROWSET(BULK 'C:\AutoCrib WebServices\XML Files\ATC\OpenPOs.XML', SINGLE_CLOB) t 

 SELECT [Company] = p.PO_X.value('(//*:Header/*:From/*:Company)[1]', 'varchar(150)')
     , [Identity] = f.value('(*:Identity)[1]', 'varchar(200)')
     , [DatabaseNM] = f.value('(*:DatabaseName)[1]', 'varchar(50)')
     , [PoNo] = r.value('(*:PoNo)[1]', 'varchar(10)')
     , [ItemLine] = i.value('(*:Line)[1]', 'int')
     , [ItemTagNo] = i.value('(*:TagNo)[1]', 'varchar(10)')
     , [ItemVendorID] = i.value('(*:VendorID)[1]', 'varchar(100)')
     , [ItemID] = i.value('(*:ItemID)[1]', 'varchar(30)')
     , [ItemRFID] = i.value('(*:ItemRFID)[1]', 'varchar(50)')
     , [ItemStation] = i.value('(*:Station)[1]', 'varchar(5)')
     , [ItemBin] = i.value('(*:Bin)[1]', 'varchar(30)')
     , [ItemOrderQty] = i.value('(*:OrderQty)[1]', 'int')
     , [ItemReceived] = i.value('(*:Received)[1]', 'int')
     , [ItemReceivedBurn] = i.value('(*:ReceivedBurn)[1]', 'int')
     , [ItemPackQty] = i.value('(*:PackQty)[1]', 'int')
     , [ItemUnitCost] = i.value('(*:UnitCost)[1]', 'money')
     , [ItemUnitPrice] = i.value('(*:UnitPrice)[1]', 'money')
     , [ItemOrderDate] = i.value('(*:OrderDate)[1]', 'datetime')
     , [ItemDueDate] = i.value('(*:DueDate)[1]', 'datetime')
     , [ItemPromiseDate] = i.value('(*:PromiseDt)[1]', 'datetime')
     , [ItemReceiveDate] = i.value('(*:ReceiveDt)[1]', 'datetime')
     , [ItemProcessed] = i.value('(*:Processed)[1]', 'varchar(10)')
FROM #PO p
    OUTER APPLY p.PO_X.nodes('//*:Header/*:From') a(f)
    OUTER APPLY p.PO_X.nodes('//*:Request/*:OpenPurchaseOrderRequest') c(r)
    OUTER APPLY p.PO_X.nodes('//*:Request/*:OpenPurchaseOrderRequest/*:LineItems/*:Item') l(i) 
DROP TABLE #PO

CodePudding user response：

Start with loading the file. This uses OPENROWSET, but you might find BULK INSERT more flexible.

    SELECT DepX = CAST(BulkColumn AS xml)
    INTO #Departments
      FROM OPENROWSET(BULK 'C:\Temp\Departments.XML', SINGLE_CLOB) t 
    
    --SINGLE_CLOB|SINGLE_NCLOB for Ascii vs Unicode

Now, we can query the xml

    SELECT [From.Company] = d.DepX.value('(//*:Header/*:From/*:Company)[1]', 'varchar(99)')
            /* Shorter paths if we OUTER APPLY the root we're interested in */
         , [From.Company Shortcut] = f.value('(*:Company)[1]', 'varchar(99)')
      FROM #Departments d
      OUTER APPLY d.DepX.nodes('//*:Header/*:From') a(f)

Results

For help with xml shredding, try Jacob Sebastian's SELECT * FROM XML The output is helpful, but taking the time to go through and understand how it works is very educational.

This uses namespace wildcard *:
More complex xml might require WITH XMLNAMESPACE

CodePudding user response：

I was finally able to figure it out. I was missing the namespace. Here's my SQL logic:

DECLARE @XML XML = 
    (SELECT *
     FROM OPENROWSET(BULK 'C:\Temp\OpenPOs.XML', SINGLE_CLOB) t)

;WITH XMLNAMESPACES (DEFAULT 'https://www.autocrib.net/acXMLSchema.xsd')
SELECT A.evnt.value('(PoNo/text())[1]','varchar(10)') AS Event_DriverId
      ,B.rec.value('(Line/text())[1]','int') AS Record_RecordId 
FROM @XML.nodes('/acXML/Request/OpenPurchaseOrderRequest') A(evnt)
    OUTER APPLY A.evnt.nodes('LineItems/Item') B(rec);