SQL query op XML data

In de ESB Portal worden fouten opgeslagen incl. het bericht dat de fout heeft veroorzaakt en voor resubmit kan worden aangeboden. Berichten worden opgeslagen in kolom [MessageData] met als formaat [varchar](max). De eerste stap is dat de namespaces worden opgezocht in het receipt advice schema. Vervolgens kunnen de juiste xpath queries worden opgesteld. Let op dat er gebruik wordt gemaakt van twee functies: Xml.Value (één waarde) en Xml.Query (meerdere waarden).

USE ErpBTProd_EsbExceptionDb GO

WITH XMLNAMESPACES
(
‘http://schemas.rfsholding.com/rid/ReceiptAdvice/v1.0’ as ra , ‘http://schemas.rfsholding.com/rid/ReceiptAdviceExtensionComponents/v1.0’ as ext , ‘http://schemas.rfsholding.com/rid/ExtendedReceiptAdvice/v1.0’ as extra , ‘http://schemas.rfsholding.com/rid/ExtendedReceiptAdviceAggregateComponents/v1.0’ as agg , ‘http://schemas.rfsholding.com/rid/ReceiptAdviceAggregateComponents/v1.0’ as cac , ‘http://schemas.rfsholding.com/rid/CommonBasicComponents/v1.0’ as cbc
)

SELECT F.[DateTime], F.BusinessTransactionID, F.MessageType, F.BusinessEntityID, F.FaultCode, F.ExceptionMessage,
CAST(md.MessageData AS XML).value(‘data(//ra:ReceiptAdvice/ext:UBLExtensions/ext:UBLExtension/ext:ExtensionContent/extra:ExtendedReceiptAdvice/agg:ReceiptLine/cac:Location/cac:SubsidiaryLocation/cbc:ID)[1]’, ‘varchar(50)’) AS Delivery,
CAST(md.MessageData AS XML).query(‘data(//ra:ReceiptAdvice/cac:DespatchSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID)[1]’) AS Klant,
CAST(md.MessageData AS XML).query(‘data(//ra:ReceiptAdvice/cac:ReceiptLine/cac:Item/cac:BuyersItemIdentification/cbc:ID)’) AS ArtikelNr,
CAST(md.MessageData AS XML).query(‘data(//ra:ReceiptAdvice/cac:ReceiptLine/cac:OrderLineReference/cac:OrderReference/cbc:ID)’) AS SalesOrder FROM Fault F (nolock)
JOIN (Message M (nolock)
JOIN MessageData MD ON M.MessageID = MD.MessageID)
ON F.FaultID = M.FaultID
WHERE F.FaultStatus = 0
AND F.MessageType = ‘ReceiptAdvice’
AND CAST(md.MessageData AS XML).value(‘data(//ra:ReceiptAdvice/ext:UBLExtensions/ext:UBLExtension/ext:ExtensionContent/extra:ExtendedReceiptAdvice/agg:ReceiptLine/cac:Location/cac:SubsidiaryLocation/cbc:ID)[1]’, ‘varchar(50)’) in (‘DED_RIB’, ‘MAU_RIB’)
ORDER BY F.[DateTime]

Leave a Reply

Your email address will not be published. Required fields are marked *