Abfrage von XML-data mit Sql server

Ich habe einige XML-fileen, die ich zu parsing und zu relationalen data zu konvertieren. Die data sollen in SQL server gespeichert werden. Ich bekomme die fileen aus verschiedenen Quellen und ich laufe in Schwierigkeiten mit einem von ihnen, weil das Segment für die Bestellnummer wiederholt wird. Die Mühe, die ich habe, ist, dass es eine doppelte Aufzeichnung oder Zeile erstellt. Hier sieht das xml aus.

<?xml version="1.0" encoding="UTF-8"?> <pt:PTShipmentNotification xmlns:cmn="urn:tracelink:mapper:sl:commontypes" xmlns:pt="urn:tracelink:mapper:sl:product_track"> <pt:MessageBody> <pt:ShipmentTransaction> <cmn:ShipmentIdentifiers> <cmn:ShipmentId type="ShipmentNumber">22584</cmn:ShipmentId> <cmn:ShipmentId type="BillOfLading">2584226516</cmn:ShipmentId> </cmn:ShipmentIdentifiers> </pt:ShipmentTransaction> <pt:ShipmentItemDetails> <cmn:LineItemNumber>1</cmn:LineItemNumber> <cmn:LotNumber>FT0109</cmn:LotNumber> <cmn:OrderNDC type="NDC542">49884066009</cmn:OrderNDC> <cmn:ExpirationDate>2018-09-30</cmn:ExpirationDate> <cmn:SalesQuantity quantityUnitOfMeasure="EA">12</cmn:SalesQuantity> <pt:ReferenceDocuments> <cmn:BusinessDocument type="PurchaseOrder">024136</cmn:BusinessDocument> <cmn:DocumentDate>2016-02-03</cmn:DocumentDate> </pt:ReferenceDocuments> <pt:ReferenceDocuments> <cmn:BusinessDocument type="PurchaseOrder">024136</cmn:BusinessDocument> <cmn:DocumentDate>2016-02-03</cmn:DocumentDate> </pt:ReferenceDocuments> </pt:ShipmentItemDetails> <pt:ShipmentItemDetails> <cmn:LineItemNumber>2</cmn:LineItemNumber> <cmn:LotNumber>FN0043</cmn:LotNumber> <cmn:OrderNDC type="NDC542">49884082710</cmn:OrderNDC> <cmn:ExpirationDate>2019-01-31</cmn:ExpirationDate> <cmn:SalesQuantity quantityUnitOfMeasure="EA">36</cmn:SalesQuantity> <pt:ReferenceDocuments> <cmn:BusinessDocument type="PurchaseOrder">024136</cmn:BusinessDocument> <cmn:DocumentDate>2016-02-03</cmn:DocumentDate> </pt:ReferenceDocuments> <pt:ReferenceDocuments> <cmn:BusinessDocument type="Invoice">024136</cmn:BusinessDocument> <cmn:DocumentDate>2016-02-03</cmn:DocumentDate> </pt:ReferenceDocuments> </pt:ShipmentItemDetails> </pt:MessageBody> </pt:PTShipmentNotification> 

Hier ist die Abfrage, die ich verwende, um die file in eine XML-Variable zu laden und dann über SQL server Management Studio abzufragen. In der Produktion mache ich es mit SSIS.

 DECLARE @XmlFile XML SELECT @XmlFile = BulkColumn FROM OPENROWSET(BULK 'C:\Files\in\badxml.xml', SINGLE_BLOB) x ;WITH XMLNAMESPACES ( 'urn:tracelink:mapper:sl:commontypes' AS cmn, 'urn:tracelink:mapper:sl:product_track' AS pt ) select T.data.value('@type', 'varchar(20)') AS ShipType, T.data.value('.', 'varchar(35)') AS ShipNum, n.value('../cmn:LineItemNumber[1]','VARCHAR(30)') AS LineItem, n.value('../cmn:OrderNDC[1]/@type', 'varchar(20)' ) AS OrderNDC, n.value('../cmn:OrderNDC[1]','VARCHAR(30)') AS NDC, n.value('../cmn:LotNumber[1]','VARCHAR(30)') AS Lot, n.value('../cmn:SalesQuantity[1]/@quantityUnitOfMeasure','VARCHAR(30)') AS UOM, n.value('../cmn:SalesQuantity[1]','VARCHAR(30)') AS QTY, n.value('cmn:BusinessDocument[0]','VARCHAR(50)') AS PO, GETDATE() ProcessedDt, 0 ProcessedFlag, @XmlFile from @XmlFile.nodes('pt:PTShipmentNotification/pt:MessageBody/pt:ShipmentTransaction/cmn:ShipmentIdentifiers/cmn:ShipmentId') T(data) CROSS APPLY @XmlFile.nodes('pt:PTShipmentNotification/pt:MessageBody/pt:ShipmentItemDetails/pt:ReferenceDocuments') x1(n) WHERE T.data.value('@type', 'varchar(20)') = 'ShipmentNumber' AND n.value('cmn:BusinessDocument[1]/@type','VARCHAR(50)') = 'PurchaseOrder' 

Verweis auf die xml oben, Wenn das zweite Vorkommen der Bestellnummer etwas anderes wie Rechnungsnummer genannt wird (wie es normalerweise ist), dann gibt es kein Problem. Ich bekomme nur einen datasatz oder eine Zeile pro Zeile Artikelnummer. Ich versuche, den Absender zu bekommen, um die file zu reparieren, aber ich bin auch daran interessiert, den Code zu überarbeiten, um das Problem zu behandeln. Also für Werbebuchung man in der oben xml bekomme ich zwei datasätze. Für Werbebuchung 2 bekomme ich nur einen datasatz. Hat jemand irgendwelche Vorschläge für die Änderung der Abfrage, um das Problem zu korrigieren?

Versuche Kreuzung nur auf eine einzige Zeile, anstatt zu versuchen, in der wo zu filtern.

 CROSS APPLY @XmlFile.nodes('pt:PTShipmentNotification/pt:MessageBody/pt:ShipmentItemDetails/pt:ReferenceDocuments[@type=''PurchaseOrder''][1]') x1(n) 

Entfernen Sie den Filter auf PurchaseOrder.

 DECLARE @XmlFile XML SELECT @XmlFile = BulkColumn FROM OPENROWSET(BULK 'C:\Files\in\badxml.xml', SINGLE_BLOB) x ;WITH XMLNAMESPACES ( 'urn:tracelink:mapper:sl:commontypes' AS cmn, 'urn:tracelink:mapper:sl:product_track' AS pt ) select T.data.value('@type', 'varchar(20)') AS ShipType, T.data.value('.', 'varchar(35)') AS ShipNum, n.value('../cmn:LineItemNumber[1]','VARCHAR(30)') AS LineItem, n.value('../cmn:OrderNDC[1]/@type', 'varchar(20)' ) AS OrderNDC, n.value('../cmn:OrderNDC[1]','VARCHAR(30)') AS NDC, n.value('../cmn:LotNumber[1]','VARCHAR(30)') AS Lot, n.value('../cmn:SalesQuantity[1]/@quantityUnitOfMeasure','VARCHAR(30)') AS UOM, n.value('../cmn:SalesQuantity[1]','VARCHAR(30)') AS QTY, n.value('cmn:BusinessDocument[0]','VARCHAR(50)') AS PO, GETDATE() ProcessedDt, 0 ProcessedFlag, @XmlFile from @XmlFile.nodes('pt:PTShipmentNotification/pt:MessageBody/pt:ShipmentTransaction/cmn:ShipmentIdentifiers/cmn:ShipmentId') T(data) CROSS APPLY @XmlFile.nodes('pt:PTShipmentNotification/pt:MessageBody/pt:ShipmentItemDetails/pt:ReferenceDocuments[@type=''PurchaseOrder''][1]') x1(n) WHERE T.data.value('@type', 'varchar(20)') = 'ShipmentNumber'