On a table field, example :
1
select field from table
the field type is XML, a sample record :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--sample xml
<?xml version="1.0" encoding="UTF-8"?>
<item id="9055">
<resource>
<size>
<imagetype id="1" code="Large"></imagetype>
<imagetype id="2" code="Normal"></imagetype>
</size>
<meta>
<lang id="1" code="el"></lang>
<lang id="9" code="el"></lang>
</resource>
</item>
Group By
select the Meta.code only
1
2
3
4
5
--src https://stackoverflow.com/a/19165348
SELECT
X.Y.value('@code', 'varchar(3)')
FROM table imr
OUTER APPLY imr.field.nodes('Item/Resource/Meta/lang') as X(Y)
group by Meta.code to get the unique values of the xml attribute
if you do
1
2
3
4
5
SELECT
X.Y.value('@code', 'varchar(3)') AS lng
FROM table imr
OUTER APPLY imr.field.nodes('Item/Resource/Meta/lang') as X(Y)
GROUP BY X.Y.value('@code', 'varchar(3)')
will get
Msg 4148, XML methods are not allowed in a GROUP BY clause.
use it as subquery then group by the field
1
2
3
4
5
6
7
8
9
--src https://stackoverflow.com/a/22662302
SELECT lng
FROM (
SELECT
X.Y.value('@code', 'varchar(3)') AS lng
FROM table imr
OUTER APPLY imr.field.nodes('Item/Resource/Meta/lang') as X(Y)
) AS t
GROUP BY lng
Query all records have attribute equal to
this will scan all lang sub nodes of Meta element for the given attribute (aka id)
1
2
3
4
5
--src https://www.sqlshack.com/filtering-xml-columns-using-xquery-in-sql-server/
SELECT
COUNT(*)
FROM table
WHERE field.exist('(Item/Resource/Meta/lang/@id[.="9"])') = 1
the below, scans for each record, the second element Meta/lang for the given attribute (aka id)
1
2
3
4
SELECT
COUNT(*)
FROM table
WHERE field.value('(Item/Resource/Meta/lang/@id)[2]', 'varchar(3)') <> '9'
Insert extra element with attributes to existing XML record
1
2
3
4
5
--src https://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/
-- https://www.sqlshack.com/different-ways-to-update-xml-using-xquery-in-sql-server/
UPDATE table
SET field.modify('insert <lang id="9" code="ro"></lang> into (Item/Resource/Meta)[1]')
WHERE id=8
origin - https://www.pipiscrew.com/?p=18545 sqlserver-working-with-xml-field