Find if element exists in XML column of a SQL Server table

Question

I have data serialized to a XML column in a SQL Server table in the form:

<Form title="Sample Data">
  <MyProperty>1234</MyProperty>
  <Employee>
     <MyProperty>1234</MyProperty>
  </Employee>
</Form>

The element MyProperty can either be at the root level, or a child element under <Employee>.

What I need is a SQL script that:

Checks to see if MyProperty exists at the root level of the XML (I don't care about the child version of it)
If it doesn't exist, insert the record at the root level.

The value of MyProperty will have been previously calculated and I plan to place it into a temp table, along with the PK of the row that has the serialized XML.

Can anyone give some guidance on how to do this?

One question at a time please, especially when you have no idea where or how to start. What are you trying to achieve first? I assume : "checks to see if MyProperty exists at the root level of the XML". Read about exist() method and try to implement it for your case — har07
– har07, Commented Dec 9, 2015 at 1:08

JosephStyons · Accepted Answer · 2015-12-09 05:01:23Z

1

The first question may be answered by using simple XPATH syntax as shown below.

declare @x xml = '<Form title="Sample Data">
  <MyProperty>1234</MyProperty>
  <Employee>
     <MyProperty>1234</MyProperty>
  </Employee>
</Form>';


--does MyProperty exist at the root level?
declare @rootlevelvalue nvarchar(max) = (select T.c.value('(MyProperty)[1]','nvarchar(max)') from @x.nodes('/Form') T(c));
if @rootlevelvalue is not null
begin
  print 'yes, it exists at the root level (value = ' + @rootlevelvalue + ')';
end
else
begin
  print 'no, it does not exist at the root level'
end

And the second question can be answered with XPATH's insert syntax.

declare @y xml = '<Form title="Sample Data">
  <Employee>
     <MyProperty>1234</MyProperty>
  </Employee>
</Form>';


--does MyProperty exist at the root level?
declare @rootlevelvalue nvarchar(max) = (select T.c.value('(MyProperty)[1]','nvarchar(max)') from @y.nodes('/Form') T(c));
if @rootlevelvalue is not null
begin
  print 'yes, it exists at the root level (value = ' + @rootlevelvalue + ')';
end
else
begin
  print 'no, it does not exist at the root level';
  set @y.modify('insert <MyProperty>99999</MyProperty>into (/Form)[1]')
  print 'so we added it.';
end
select @y

edited Dec 9, 2015 at 5:01

answered Dec 9, 2015 at 4:55

JosephStyons

59.1k64 gold badges169 silver badges240 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CorribView Over a year ago

Thanks, regarding the first part: I used .exist() to check if the element exists in the XML as suggested by har07.

Collectives™ on Stack Overflow

Find if element exists in XML column of a SQL Server table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related