Getting Null value for SQL xml cast in parsing a element

Question

getting Null value for xml cast in parsing a element "ErrorType"

I have this XML stored in my SQL database column.

<Response>
    <ISSuccessfulPingResponse>false</ISSuccessfulPingResponse>
    <PingResponse>
        <PriceResult xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://www.test.com/pasd/test.xsd">
            <Result>ERROR</Result>
            <ErrorType>XML Validation</ErrorType>
            <ErrorDescription> equal to 5.</ErrorDescription>
            <out>0</out>
        </PriceResult>
    </PingResponse>
</Response>

and I tried to run an SQL script for getting value for element "ErrorType", but I am receiving null value.

SELECT  Cast(Result as xml).value ('(/Response/PingResponse/PriceResult/ErrorType)[1]', 'varchar(100)' ) as result  from  getresults
where Id=23

har07 · Accepted Answer · 2016-02-09 08:59:12Z

2

Since PriceResult and all of the descendant elements without prefix (including ErrorType) are in the namespace https://www.test.com/pasd/test.xsd, so you should be able to use WITH XMLNAMESPACES here, for example :

;WITH XMLNAMESPACES('https://www.test.com/pasd/test.xsd' as d)
SELECT Cast(Result as xml).value ('
    (/Response/PingResponse/d:PriceResult/d:ErrorType)[1]
', 'varchar(100)' ) as result 
from getresults where Id=23

Basically, the codes defined a prefix d that mapped to the namespace URI using WITH XMLNAMESPACES, and then use it (prefix d) to reference elements in the namespace.

answered Feb 9, 2016 at 8:59

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting Null value for SQL xml cast in parsing a element

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related