CODEWITHSUNDEEP
sql-serverxmlt-sqlxqueryxquery-sql
Denis Kaminsky
Denis Kaminsky

Reputation: 17

How to exclude specific nodes from XQuery search?

I have following script which actually will add Test="Value" attribute to all nodes in xml document:

declare @xml XML = '
<root>
    <text><b>test2</b></text>
    <text>test</text>
    <text>test</text>
    <text>test</text>
</root>
'
while @xml.exist('//*[not(@TEST)]') = 1
begin
    set @xml.modify('
        insert attribute TEST {"value"}
        into (//*[not(@TEST)])[1]
    ')
end
select @xml

But how to exclude specific nodes from this query? I want to apply this attribute to all nodes but "a", "b"

Upvotes: 0

Views: 234

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

Please try the following solution.

SQL

DECLARE @xml XML = 
N'<root>
    <text><b>test2</b></text>
    <text>test</text>
    <text>test</text>
    <text>test</text>
</root>;
'
WHILE @xml.exist('//*[not(local-name(.)=("a","b"))][not(@TEST)]') = 1
BEGIN
    SET @xml.modify('
        insert attribute TEST {"value"}
        into (//*[not(local-name(.)=("a","b"))][not(@TEST)])[1]
    ');
END;

-- test
SELECT @xml;

Output

<root TEST="value">
  <text TEST="value">
    <b>test2</b>
  </text>
  <text TEST="value">test</text>
  <text TEST="value">test</text>
  <text TEST="value">test</text>
</root>;

Upvotes: 1

Related Questions