开发者

Using XQuery in SQL Server 2008 to search for content

开发者 https://www.devze.com 2022-12-20 11:36 出处:网络
Take the following 4 example XML documents: <Example> <Colour>orange</Colour> </Example>

Take the following 4 example XML documents:

  1. <Example> <Colour>orange</Colour> </Example>

  2. <Example> <Car colour="orange">Ford Focus</Car> </Example>

  3. <Example> <County>Orange County</County> </Example>

  4. <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:

  1. this has a value orange inside an element.
  2. this has a value orange inside an attribute.
  3. 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:

  1. 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%'
    
  2. 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!

0

精彩评论

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