Archive for the ‘XML Query’ Category

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). SOAP_XML_Message_Example

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 »

%d bloggers like this: