Take the following 4 example XML documents:
<Example> <Colour>orange</Colour> </Example>
<Example> <Car colour="orange">Ford Focus</Car> </Example>
<Example> <County>Orange County</County> </Example>
<Example> <Orange>555</Orange> </Example>
These are all stored in a SQL Server database in a table with a XML datatype column (untyped).
How would I go about running a query looking for all content 开发者_如何学Cin the document with the word orange in it, which would return the following documents:
- this has a value orange inside an element.
- this has a value orange inside an attribute.
- this has a value Orange County inside an element (note different casing of the word Orange)
Document 4 should not be returned in the query results as the word orange is an element name and is not a data value.
Is this even possible?
Thanks in advance.
I don't think you can do it in a single query - however, with two, you should get the results you're looking for:
first query to get all XML nodes that contain a text (inside the element) that looks like "orange":
SELECT * FROM dbo.XQueryTest WHERE XmlContent.value('(//*/text())[1]', 'varchar(50)') LIKE '%orange%'
second query to do the same, but based on an attribute value:
SELECT * FROM dbo.XQueryTest WHERE XmlContent.value('(//*/@*)[1]', 'varchar(50)') LIKE '%orange%'
Query 1 just grabs the value of the text()
for all XML nodes as a string (varchar(50)
) and compares that based on regular SQL syntax against '%orange%'.
Query no. 2 grab all the attribute values (/@*
) and does the same.
Of course, you can UNION
those two together, if you really need to.
Hope this helps!
精彩评论