开发者

Matching a value to a field in XML using XQuery

I have a bunch of rows in a database th开发者_运维问答at all contain an element that looks like the following

<ModuleData ModuleFieldGU="4dcdde21-40c6-4d1a-9fe1-1841935d3a68">
 <DateTime>0001-01-01T00:00:00</DateTime>
 <Boolean>false</Boolean>
 <Int>0</Int>
 <Varchar>Test District</Varchar>
 <GUID>00000000-0000-0000-0000-000000000000</GUID>
 <Modified>true</Modified>
 </ModuleData>

I need to look across all of these and match where the varchar element/node is set to a certain value.

I've tried both of the followuing, and neither seem to be working for me

SELECT 
    Data.Element.value('(@Varchar)[1]', 'varchar(255)') as UserEmail 
    FROM
        ModuleData
    CROSS APPLY
        XML_DATA.nodes('/ModuleData') AS Data(Element)
    WHERE
    Data.Element.value('(@Varchar)[1]', 'varchar(255)') = @UserEmail

and

SELECT 
   CAST(XML_DATA.query('data(/ModuleData[@Varchar])') as varchar(255)) as UserEmail
FROM 
   ModuleData
WHERE 
   XML_DATA.exist('/ModuleData[@Varchar = sql:variable("@UserEmail")]') = 1

but it isn't working. I'm not really up to speed with the finer points of using XQuery to get data out of XML. Can anyone point me in the correct direction?


The correct XPath should be:

/ModuleData/Varchar

Because it's an element, not an attribute.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜