CODEWITHSUNDEEP
sqlxml
Harag
Harag

Reputation: 1582

SQL check XML node exists via a variable, add & delete

I'm looking for a way to check if a NODE exists in an xml data type using MSSQL 2012, I want to past the node to check for as a varchar into a stored procedure. I've tried something like the below, but it doesn't appear to work. What I want to do is look for the node "var2" (passed into the SP as varchar), and if it exists return true, if it doesn't exist insert it into the root. All the examples I've found have the node hardcoded, which works.

Code with node as a variable:

declare @node varchar(255)
declare @x xml

set @node = 'var2'
set @x='<root><var1>0</var1><var2>123</var2><var3>1000</var3><var4>989</var4></root>'

select  @x.exist('//.[node()=(sql:variable("@node"))]') -- always returns false

Code with hardcoded node:

select  @x.exist('//var2')  -- returns true

if node exists return true, else insert node into root element.

EDIT:

I've worked out I can do the following:

select @x.exist('//*[local-name()=sql:variable("@node")]') -- returns 1 if node exists

If the node doesn't exist I can add a new node with:

set @add = '<newNode>test</newNode>'
@x.modify('insert sql:variable("@add") into (/root)[1]')

I can remove existing nodes with:

set @del ='newNode'
@x.modify('delete /root/*[local-name()=sql:variable("@del")]')

but can't get the delete to work.

Upvotes: 1

Views: 3730

Answers (1)

Harag
Harag

Reputation: 1582

I've worked out the answer to my own question:

--To check if a NODE exists
declare @node varchar(255)
set @node ="existingnode"
select @x.exist('//*[local-name()=sql:variable("@node")]')

--To ADD a new Node:
declare @add as xml
set @add='<abc>testingtesting</abc>'
@x.modify('insert sql:variable("@add") into (/root)[1]')

-- To Delete a node:
declare @del as varchar(255)
set @del='abc'
@x.modify('delete /root/*[local-name()=sql:variable("@del")]')

The reason for my question and answer is because I wanted to work on the node names, not the attribute values.

Upvotes: 2

Related Questions