SQL query to retrive value from XMl xolumn

Question

I have a column in table with datatype XML. mycolumn has following value

 <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www

.w3.org/2001/XMLSchema" 
xmlns:ns1="http://http://localhost/test/">
    <SOAP-ENV:Body>
        <ns1:IDResults>
            <NCNL>
                <Value>123</Value>
            </NCNL>
        </ns1:IDResults>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

select mycolumn.query('./Envelope/Body')

My above select statement does not return anything.

Do I need to specify SOAP-ENV: prefix, if yes than how do I specify it?

Thanks in advance.

score 0 · Accepted Answer · answered Jun 27 '14 at 14:16

0

The actual query depends on what you want to get, but basically you have to specify namespace in your query, like this:

;with xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' as [SOAP-ENV])
select @data.query('SOAP-ENV:Envelope/SOAP-ENV:Body/*')

answered Jun 27 '14 at 14:16

Roman Pekar

107,110
28
195
197

Thank you very much for the answer. It's working using your suggestion. – user3756938 Jun 30 '14 at 08:51

SQL query to retrive value from XMl xolumn

1 Answers1