Remove xml tag when its attribute is empty using mssql

Question

I have following xml content in a column,

<Certification name="ACT" type=""/>
<Certification name="CERTIFIED PEDIATRIC NURSE" type="Certification"/>
<Certification name="LICENSED VOCATIONAL NURSE (LVN)" type="License"/>

My question is when I find empty in 'type' atrribute , that single tag should be eleminated.

for example I need output like follows,

  <Certification name="CERTIFIED PEDIATRIC NURSE" type="Certification"/>
  <Certification name="LICENSED VOCATIONAL NURSE (LVN)" type="License"/>

In the above output empty 'type' attribute has been removed.

So could anyone suggest how to do it using sql query?

Is this to be a permanent change or just changing the result returned in a SELECT (but leaving the data as-is within the table)? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 19, 2014 at 13:01

Hoots · Accepted Answer · 2014-06-19 13:47:50Z

1

You should be able to do something like this...

DECLARE @xml as table(xmlData xml)

insert into @xml(xmlData)
select '<Certification name="ACT" type=""/><Certification name="CERTIFIED PEDIATRIC NURSE" type="Certification"/><Certification name="LICENSED VOCATIONAL NURSE (LVN)" type="License"/>'

SELECT xmlData.query('/Certification[@type!=""]') as filteredXml
FROM   @xml T

As you've got it already in a table column you can just use the last select statement substituting @xml and xmlData with your table name and table field respectively

answered Jun 19, 2014 at 13:47

Hoots

1,9961 gold badge15 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Remove xml tag when its attribute is empty using mssql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related