SQL Query Column with XML Data

Question

I have the following data in a column, how can I query the values inside.

<deliveryPart notificationId="12345">
    <message address="[email protected]" content="multipart/alternative" domain="email.com" format="0" id="159436637" recipient-id="098765" targetCode="__MAIN__">
        <custom>
            <recipient Nickname="mynickname" id="54321" />
            <targetData Incident_Id="1509403" reference_nb="0000-0000" />
        </custom>
    </message>
</deliveryPart>

I gave it a quick go but I've never done it before and I am in short of time.

select top 10 *
from [db].[db].[table]
where mData.value('(deliveryPart/message/recipient-id)[1]','varchar(max)') like '098765'

I get the following error

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "mData" or the user-defined function or aggregate "mData.value", or the name is ambiguous.

UPDATE

I am using the following code to fetch the xml values and it works

SELECT TOP 1000 B1.[mData].value('(deliveryPart/message/@id)[1]', 'NVARCHAR(MAX)') AS 'MessageID', B1.[mData].value('(deliveryPart/message/@address)[1]', 'NVARCHAR(MAX)') AS 'Address'
  FROM (SELECT CAST(mData as XML) as xmlData FROM [dbo].[db].[table]) B0
  CROSS APPLY xmlData.nodes('/') B1 (mData)
  WHERE B1.[mData].value('(deliveryPart/message/@address)[1]', 'NVARCHAR(MAX)') LIKE '%@%'

And it returns the xml values store in the ntext field just fine.

180646774   [email protected]
159436627   [email protected]
159436637   [email protected]

However, I need to fetch values from outside the mData column and is not letting me do it.

marc_s · Accepted Answer · 2015-04-08 17:17:13Z

1

You need to use the @recipient-id as a XML attribute - not XML element:

<message address="[email protected]" content="multipart/alternative" 
         domain="email.com" format="0" id="159436637" 
         recipient-id="098765" targetCode="__MAIN__">
         ************  this is an *attribute* - use the `@` to select it!

Code:

select top 10 *
from [db].[db].[table]
where mData.value('(deliveryPart/message/@recipient-id)[1]', 'varchar(max)') like '098765'

answered Apr 8, 2015 at 17:17

marc_s

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

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

3 Comments

David Garcia Over a year ago

It still returns the same error, the data type of the mData column is ntext.

marc_s Over a year ago

@J.D: First of all, ntext is deprecated - stop using it. Secondly: if this is XML - why aren't you using the XML datatype? That's the best suited datatype - and that's the only datatype that supports these XQuery expressions ....

David Garcia Over a year ago

Hi Marc, this came by default with the crm tool, I managed to get some sort of result by casting the column as xml, SELECT cast(mData as XML).query('deliveryPart/message[@recipient-id="12345"]') as xmlData, * FROM [dbo].[data].[table]

Collectives™ on Stack Overflow

SQL Query Column with XML Data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related