How to query xml value inside a xml column in SQL server

Question

I have something like following code inside [XMLValue] column of a table called "AlgorithmLog":

<?xml version="1.0" encoding="utf-8"?>
<AdapterInfo xmlns:i="http://www.w3.org/2001/XMLSchema-instance"     
            xmlns:d1p1="http://schemas.datacontract.org/2004/07/Adapters.Adapter.CloudTrader"    
            xmlns="http://schemas.datacontract.org/2004/07/Adapters.Adapter"  
            i:type="d1p1:AlgorithmStatusReport">
    <SequenceNumber>0</SequenceNumber>
    <TrackingGuid i:nil="true" />
    <d1p1:Broker>Default</d1p1:Broker>
    ...
       <d1p1:XMLValue>&lt;?xml version="1.0"?&gt;&lt;int xmlns="http://schemas.microsoft.com/2003/10/Serialization/"&gt;1900&lt;/int&gt;</d1p1:XMLValue>
</AdapterInfo>

and I want to get the value "1900" inside the node <d1p1:XMLValue>

So here is my query:

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Adapters.Adapter' AS x, 
                   'http://schemas.datacontract.org/2004/07/Adapters.Adapter.CloudTrader' As p,
                   'http://schemas.microsoft.com/2003/10/Serialization/'as w)
   SELECT  
       XMLValue.query('(/x:AdapterInfo/p:XMLValue/w:int)[1]')AS [XMLVaule]
   FROM AlgorithmLog

But it returns nothing.

Could anyone tell me where I did wrong or how I can do it?

Thank you.

marc_s · Accepted Answer · 2012-07-03 21:03:59Z

2

Since you have "encoded" XML inside another XML node, and you cannot automatically cast to the XML datatype using the .value() XQuery method, it all gets a bit involved - but this seems to work for me:

;WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Adapters.Adapter' AS x, 
                    'http://schemas.datacontract.org/2004/07/Adapters.Adapter.CloudTrader' As p,
                    'http://schemas.microsoft.com/2003/10/Serialization/'as w)
   SELECT  
       CAST(XmlContent.value('(/x:AdapterInfo/p:XMLValue)[1]', 'varchar(2000)') AS XML).value('(w:int)[1]', 'int') AS [XMLValue]
   FROM AlgorithmLog
   WHERE .......  -- use whatever condition makes sense for you here

answered Jul 3, 2012 at 21:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

How to query xml value inside a xml column in SQL server

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