CODEWITHSUNDEEP
sqlsql-serverxml
Rocky
Rocky

Reputation: 309

Remove xml tag when its attribute is empty using mssql

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?

Upvotes: 0

Views: 551

Answers (1)

Hoots
Hoots

Reputation: 1976

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

Upvotes: 1

Related Questions