CODEWITHSUNDEEP
sqlsql-serverxml
user10140546
user10140546

Reputation: 380

SQL How to get value of XML data column and use it inside of the where clause

I have a table with an XML data type column called Settings. The structure of the XML data:

<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
...
<root>

The table also has an ID column that I would like to use to group my results. I want to count how many Setting1 configurations are set to true per ID. Note: The ID field is not unique and can repeat.

Here is what I have so far:

with xmlnamespaces (DEFAULT '...')
select count(Setting1) as counts
  from (select Settings.value('(/root/Setting1)[1]', 'nvarchar(max)') as Setting1
    from MY_TABLE
    group by ID) res
  where Setting1= 'true'

However I am getting the following error:

Column 'MY_TABLE.Configuration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 2

Views: 256

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Although you've got an answer already, I'd like to add some alternatives:

(Credits to Michal Turczyn for the mockup)

declare @tbl table (id int, xmlCol xml);
insert into @tbl values
(1, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>'),
(1, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>'),
(2, '<root>
<Setting1>false</Setting1>
<Setting2>false</Setting2>
</root>'),
(2, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>');

The fastest should be a simple XML.exist():

SELECT t.id,
       sum(CASE WHEN t.xmlCol.exist('/root/Setting1[text()="true"]')=1 THEN 1 ELSE 0 END) howManyTrue
FROM @tbl t
GROUP BY t.id

You can enhance this with a variable to set the SettingName outside the statement:

DECLARE @SettingName NVARCHAR(100)=N'Setting1';

SELECT t.id,
       sum(CASE WHEN t.xmlCol.exist('/root/*[local-name()=sql:variable("@SettingName") and text()="true"]')=1 THEN 1 ELSE 0 END) howManyTrue
FROM @tbl t
GROUP BY t.id

--And if a Setting might occur more than once within one XML, you might use XQuery's count():

SELECT t.id,
       sum(t.xmlCol.value('count(/root/*[local-name()=sql:variable("@SettingName") and text()="true"])','int')) howManyTrue
FROM @tbl t
GROUP BY t.id

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

Try this query:

declare @tbl table (id int, xmlCol xml);
insert into @tbl values
(1, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>'),
(1, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>'),
(2, '<root>
<Setting1>false</Setting1>
<Setting2>false</Setting2>
</root>'),
(2, '<root>
<Setting1>true</Setting1>
<Setting2>false</Setting2>
</root>');

select id,
       sum(convert(int, xmlCol.value('(/root//Setting1/node())[1]', 'bit'))) howManyTrue
from @tbl
group by id

Upvotes: 1

Related Questions