CODEWITHSUNDEEP
sqlsql-server
Cylindric
Cylindric

Reputation: 5894

Querying XML colum for values

I have a SQL Server table with an XML column, and it contains data something like this:

<Query>
  <QueryGroup>
    <QueryRule>
      <Attribute>Integration</Attribute>
      <RuleOperator>8</RuleOperator>
      <Value />
      <Grouping>OrOperator</Grouping>
    </QueryRule>
    <QueryRule>
      <Attribute>Integration</Attribute>
      <RuleOperator>5</RuleOperator>
      <Value>None</Value>
      <Grouping>AndOperator</Grouping>
    </QueryRule>
  </QueryGroup>
</Query>

Each QueryRule will only have one Attribute, but each QueryGroup can have many QueryRules. Each Query can also have many QueryGroups.

I need to be able to pull all records that have one or more QueryRule with a certain attribute and value.

SELECT * 
FROM QueryBuilderQueries 
WHERE [the xml contains any value=X where the attribute is either Y or Z]

I've worked out how to check a specific QueryRule, but not "any".

SELECT
  Query
FROM
  QueryBuilderQueries
WHERE
  Query.value('(/Query/QueryGroup/QueryRule/Value)[1]', 'varchar(max)') like 'UserToFind'
  AND Query.value('(/Query/QueryGroup/QueryRule/Attribute)[1]', 'varchar(max)') in ('FirstName', 'LastName')

Upvotes: 0

Views: 48

Answers (3)

King King
King King

Reputation: 63377

It's a pity that the SQL Server (I'm using 2008) does not support some XQuery functions related to string such as fn:matches, ... If it supported such functions, we could query right inside XQuery expression to determine if there is any. However we still have another approach. That is by turning all the possible values into the corresponding SQL row to use the WHERE and LIKE features of SQL for searching/filtering. After some experiementing with the nodes() method (used on an XML data), I think it's the best choice to go:

select *
from QueryBuilderQueries 
where exists( select *
              from Query.nodes('//QueryRule') as v(x)
              where LOWER(v.x.value('(Attribute)[1]','varchar(max)')) 
                                          in ('firstname','lastname')
                    and v.x.value('(Value)[1]','varchar(max)') like 'UserToFind')

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use two exist(). One to check the value and one to check Attribute.

select Q.Query
from dbo.QueryBuilderQueries as Q
where Q.Query.exist('/Query/QueryGroup/QueryRule/Value/text()[. = "UserToFind"]') = 1 and
      Q.Query.exist('/Query/QueryGroup/QueryRule/Attribute/text()[. = ("FirstName", "LastName")]') = 1

If you really want the like equivalence when you search for a Value you can use contains().

select Q.Query
from dbo.QueryBuilderQueries as Q
where Q.Query.exist('/Query/QueryGroup/QueryRule/Value/text()[contains(., "UserToFind")]') = 1 and
      Q.Query.exist('/Query/QueryGroup/QueryRule/Attribute/text()[. = ("FirstName", "LastName")]') = 1

Upvotes: 1

glaeran
glaeran

Reputation: 426

According to http://technet.microsoft.com/pl-pl/library/ms178030%28v=sql.110%29.aspx

"The XQuery must return at most one value"

If you are quite certain that for example your XML has let's say maximum 10 QueryRules you could maybe use WHILE to loop everything while droping your results into temporary table?

maybe below can help you anyway

CREATE TABLE #temp(
Query type)

DECLARE @i INT
SET @i = 1
WHILE @i >= 10
BEGIN
INSERT INTO #temp
    SELECT
        Query
    FROM QueryBuilderQueries
    WHERE Query.value('(/Query/QueryGroup/QueryRule/Value)[@i]', 'varchar(max)') LIKE 'UserToFind'
    AND Query.value('(/Query/QueryGroup/QueryRule/Attribute)[@i]', 'varchar(max)') IN ('FirstName', 'LastName')

  @i = @i + 1
END

SELECT
    *
FROM #temp

Upvotes: 0

Related Questions