xml.value() method in SQL Server (Getting a value inside an XML query)

Question

I have an XML Query like this:

<ChangeSet xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <Change DateTime="2011-12-02T09:01:58.3615661-08:00" UserId="3123">
      <Table ChangeType="Insert" Name="EVNT_LN_AFF">
         <Keys>
            <Key FieldName="DIR_CD" Value="NB" />
            <Key FieldName="LN_ID" Value="A" />
            <Key FieldName="EVNT_ID" Value="10T000289" />
         </Keys>
         <ChangedFields>
            <Field FieldName="DIR_CD" Previous="" Current="NB" />
            <Field FieldName="LN_ID" Previous="" Current="A" />
            <Field FieldName="EVNT_ID" Previous="" Current="10T000289" />
            <Field FieldName="UD_DTTM" Previous="" Current="12/2/2011 9:01:59 AM" />
            <Field FieldName="UD_USER_ID" Previous="" Current="3123" />
         </ChangedFields>
      </Table>

(The query goes on)

Now I want to use a statement like this:

SELECT TOP 1000 [CHG_LOG_ID]
   , [EVNT_ID]
   , [DATA_XML_TXT]
   , [UD_DTTM]
FROM [MY_PROJ].[dbo].[EVNT_CHG_LOG]
WHERE DATA_XML_TXT.value('(/ChangeSet/Change/Table/ChangedFields/UD_USER_ID)[0]','varchar(50)') like '%3123%'

But when I execute the query, I don't get any results.

From your sample XML it appears you can have multiple Change elements in the ChangeSet element. Or even multiple Table elements. Is this correct? — Asher
– Asher, Commented Dec 6, 2011 at 8:47

Adam Wenger · Accepted Answer · 2011-12-06 01:43:10Z

2

I tested the following XQuery, and it should give you what you need:

SELECT TOP 1000 [CHG_LOG_ID]
   , [EVNT_ID]
   , [DATA_XML_TXT]
   , [UD_DTTM]
FROM [MY_PROJ].[dbo].[EVNT_CHG_LOG]
WHERE DATA_XML_TXT.value('(/ChangeSet/Change/Table/ChangedFields/Field[@FieldName="UD_USER_ID"]/@Current)[1]','varchar(50)') like '%3123%'

Note: Indexing for XQuery starts at 1 instead of 0

answered Dec 6, 2011 at 1:43

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

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

1 Comment

Arian Motamedi Over a year ago

That worked. I was not sure how to use "@Current" in my code. Thanks!

Collectives™ on Stack Overflow

xml.value() method in SQL Server (Getting a value inside an XML query)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related