CODEWITHSUNDEEP
sql-serverxmlstring
RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Sql Server XML tag manipulation

I have an XML something like this.

<person>
    <dwelling>
        <street>S</street>
        <city>C</city>
    </dwelling>
    <ssn>123-232-232</ssn>
</person>

I need to transform all the node names, let's say to upper case all the tag names. What is the fastest way to achieve this within SQL Server T-SQL?

Would it be...

  1. string split by <
  2. then on each token split by > (into LeftPart and RightPart)
  3. concat "<" function(LeftPart) + ">" + RightPart
  4. merge the items from (step 3)

If you have something more nifty, I'm all eyes.

Upvotes: 1

Views: 995

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @xml xml = '
<person id="10">
    <dwelling>
        <street>S</street>
        <city>C</city>
        <emptynode></emptynode>
        <emptynode/>
    </dwelling>
    <ssn>123-232-232</ssn>
</person>
'

;with C(NodeName, NewNodeName) as
(
  select distinct T.N.value('local-name(.)', 'nvarchar(max)'),
                  upper(T.N.value('local-name(.)', 'nvarchar(max)'))
  from @xml.nodes('//*') as T(N)
)  
select @xml = replace(replace(replace(replace(cast(@xml as nvarchar(max)), 
              '<' +NodeName+'/>', '<' +NewNodeName+'/>'), -- Empty node
              '<' +NodeName+' ' , '<' +NewNodeName+' ' ), -- Start node with attribute
              '<' +NodeName+'>' , '<' +NewNodeName+'>' ), -- Start node no attribute
              '</'+NodeName+'>' , '</'+NewNodeName+'>' )  -- End node
from C

Upvotes: 5

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

My solution:

SET ANSI_WARNINGS ON;    
DECLARE @x XML = 
'<person>
    <dwelling>
        <street>A</street>
        <city>AA</city>
    </dwelling>
    <ssn>123-232-001</ssn>    
</person>    
<person>    
    <dwelling>
        <street>B</street>
        <city>BB</city>
    </dwelling>
    <ssn>123-232-002</ssn>
</person>    
<person>    
    <dwelling>
        <street>C</street>
    </dwelling>
    <ssn>123-232-003</ssn>        
</person>    
<person>    
    <dwelling>
        <city>DD</city>        
    </dwelling>
    <ssn>123-232-004</ssn>    
</person>
';

SELECT   T.XmlNode.value('(dwelling/street)[1]','VARCHAR(25)') AS [DWELLING/STREET]
        ,T.XmlNode.value('(dwelling/city)[1]','VARCHAR(25)') AS [DWELLING/CITY]
        ,T.XmlNode.value('(ssn)[1]','VARCHAR(25)') AS SSN
FROM    @x.nodes('/person') T(XmlNode)
FOR XML PATH('PERSON')

Results:

<PERSON>
  <DWELLING>
    <STREET>A</STREET>
    <CITY>AA</CITY>
  </DWELLING>
  <SSN>123-232-001</SSN>
</PERSON>
<PERSON>
  <DWELLING>
    <STREET>B</STREET>
    <CITY>BB</CITY>
  </DWELLING>
  <SSN>123-232-002</SSN>
</PERSON>
<PERSON>
  <DWELLING>
    <STREET>C</STREET>
  </DWELLING>
  <SSN>123-232-003</SSN>
</PERSON>
<PERSON>
  <DWELLING>
    <CITY>DD</CITY>
  </DWELLING>
  <SSN>123-232-004</SSN>
</PERSON>

Upvotes: 0

Related Questions