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.
精彩评论