Using xpath queries in SQL

In the ESB Portal exceptions are stored including the original message that can be resubmitted. The original message is stored in column [MessageData] with data format [varchar](max). To extract data fields from the message using xpath queries in SQL, you first have to add the XML namespaces. You can either use function Xml.Value (to get one value) en Xml.Query (to get multiple values). See the example below.

Note: [′] at the end of each namespace declaration must be replaced with a single quote. Didn’t know how to get the formatting right.

USE 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 *