How to query a SOAP XML message containing more than one namespace using XML Query (XQuery)

An example of a SOAP XML message containing 2 namespaces is shown below. I want to get the value of the SupplierName from this XML stored in an SQL database table named XMLMessageSource (MessageID int, XMLMessage xml).

The following query is used to get the SOAP version and Message Body using the namespace of http://schemas.xmlsoap.org/soap/envelope/

;with xmlnamespaces(‘http://schemas.xmlsoap.org/soap/envelope/’ as [soap])

select distinct XMLMessage.value(‘(/SOAP_Domain_Msg/Context/SOAP_Version)[1]’, ‘nvarchar(max)’) as SOAP_Version                           ,XMLMessage.value(‘(/SOAP_Domain_Msg/Body)[1]’, ‘nvarchar(max)’) as MessageBody

from XMLMessageSource

The namespace http://XYZ.com/ESB/1 is used to get the item of the message body. This ns in the ns:SupplierItem tags refers to this namespace. To get the value of the SupplierName, use the XQuery value function. Note that a *: has been used instead of an individual namespace such as ns.

;with xmlnamespaces(‘http://XYZ.com/ESB/1’ as [ns])

select distinct

CompanyCode = XMLMessage.value(‘(/SOAP_Domain_Msg/Body/*:DeliveryDockets/ns:CompanyCode)[1]’, ‘nvarchar(max)’) ,SupplierName = XMLMessage.value(‘(/SOAP_Domain_Msg/Body/*:DeliveryDockets/ns:SupplierName)[1]’, ‘nvarchar(max)’) ,QuantityAccepted = XMLMessage.value(‘(/SOAP_Domain_Msg/Body/*:DeliveryDockets/ns:QuantityAccepted)[1]’, ‘float’) from #temp

Read Full Post »