How can i search in xml document?

[Julia]

Hi,
I am storing XML document in a column

And I need to be able to run a query which retrieve all rows
in which the document contain <FirstName="Julia"> or "Julie" etc....(using
LIKE)
I can use either access 2000\xp or 2003


Thanks in advance.

 

[Rick Brandt]

Hi,
I am storing XML document in a column

And I need to be able to run a query which retrieve all rows
in which the document contain <FirstName="Julia"> or "Julie" etc....(using
LIKE)
I can use either access 2000\xp or 2003

SELECT *
FROM TableName
WHERE FieldName Like "*<FirstName=" & Chr(34) & "Julia" & Chr(34) & ">*"

 

[Julia]

Thanks
and if i want to use order by on the FirstName,is it possible?

 

[Rick Brandt]

Thanks
and if i want to use order by on the FirstName,is it possible?

Possible, but it would be a pretty inefficient query so I wouldn't recommend it
unless the table is pretty small. You would need to use InStr() and Mid() to
pull say 5 or 6 characters after the occurrence of "<FirstName=" and then sort
on that.

SELECT TableName.*
FROM TableName
ORDER BY Mid(InStr(1, FieldName, "<FirstName=") + 12), 6)