Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2013-06-07 : 11:08:28
|
| but I can't see what's wrong with this. I have a field which contains some XML - in fact a SOAP request which looks something like this<soap:Envelope xmlns:xsi="http://www.w3.org2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <NewEnquiryAutoResponse xmlns="http://www.MySite.com/WebServices"> <ExternalSystemNumber>5</ExternalSystemNumber> <ExtRef>CS1126426</ExtRef> <Service>RP99</Service> <ServiceDescription /> <Subject>RP30</Subject> <SubjectDescription /> . . . </NewEnquiryAutoResponse> </soap:Body></soap:Envelope>I'm trying to get the value of the ExtRef element - in this case CS1126426.The query I have is ;WITH xmlnamespaces ('www.MySite.com' as abc, 'http://schemas.xmlsoap.org/soap/envelope/' as soap)SELECT [SOAPXML].value('/abc:ExtRef[1]', 'Varchar(50)') as MyNumber FROM [MyTable]but I get NULL values returned. What am I missing, how can I debug this to try to work out how to fix it?thankssteve-----------Insanity: doing the same thing over and over again and expecting different results. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-07 : 11:42:46
|
You have to provide the namespace in its entirety. Not just the root:;WITH xmlnamespaces (http://www.MySite.com/WebServices' as abc,... |
 |
|
|
|
|
|
I'm sure XML is simple