Use the query method to obtain the value of the xml node, queryxml
DECLARE @result xmlSET @result='<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><ReceiveDataResponse xmlns="http://tempuri.org/"><ReceiveDataResult xmlns:a="http://schemas.datacontract.org/2004/07/Cenvation.ServiceWebsite.Datamodel" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><a:ID>17953736-852b-e511-80cc-0050568e38e0</a:ID><a:Message i:nil="true"/><a:SchemaName>contact</a:SchemaName><a:Success>true</a:Success></ReceiveDataResult></ReceiveDataResponse></s:Body></s:Envelope>'SELECT @result.query('declare namespace s="http://schemas.xmlsoap.org/soap/envelope/"; declare namespace a="http://schemas.datacontract.org/2004/07/Cenvation.ServiceWebsite.Datamodel"; declare namespace i="http://www.w3.org/2001/XMLSchema-instance"; declare default element namespace "http://tempuri.org/"; data(/s:Envelope/s:Body/ReceiveDataResponse/ReceiveDataResult/a:ID)')
This is a script for reading data from a specified node in xml. The sample data contains namespaces. Therefore, you must declare these namespaces in the query method. Otherwise, the xml file cannot be correctly parsed, if the xml data contains namespaces, you do not need to declare these namespaces in the query method.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.