[SOLVED] Best way to rename XML tag name in SQL Server

Issue

Imagine I have xml just like this:

declare @pxml xml = 
'<MediaClass>   
    <MediaStream2Client>
        <Title>Test</Title>
        <Type>Book</Type>
        <Price>1.00</Price>
    </MediaStream2Client>
</MediaClass>
'

Number of stream in tag <MediaStream2Client> can be random number from 1 to 100, so I can't simply parse everything from tag <MediaStream2Client>. Is there a way to remove any digit from this tag in SQL server using grep functionality?

Solution

XPath queries can be constructed dynamically and/or contain SQL variables or columns such as the following example...

declare @pxml xml = '<MediaClass>   
    <MediaStream1Client>
        <Title>Test1</Title>
        <Type>Book1</Type>
        <Price>1.00</Price>
    </MediaStream1Client>
    <MediaStream10Client>
        <Title>Test10</Title>
        <Type>Book10</Type>
        <Price>10.00</Price>
    </MediaStream10Client>
    <MediaStream100Client>
        <Title>Test100</Title>
        <Type>Book100</Type>
        <Price>100.00</Price>
    </MediaStream100Client>
</MediaClass>';

select
  ElementName,
  MediaStreamClient.value('(Title/text())[1]', 'nvarchar(50)') as Title,
  MediaStreamClient.value('(Type/text())[1]', 'nvarchar(50)') as [Type],
  MediaStreamClient.value('(Price/text())[1]', 'decimal(18,2)') as Price
from (
  --This is just for this example, normally you'd use a Tally Table here...
  select top 100 row_number() over (order by a.object_id, a.column_id, b.object_id, b.column_id)
  from sys.columns a, sys.columns b
) Tally(N)
cross apply (select concat('MediaStream', N, 'Client')) dyn(ElementName)
cross apply @pxml.nodes('/MediaClass/*[local-name(.) = sql:column("ElementName")]') MediaClass(MediaStreamClient);

This returns the results:

ElementName	Title	Type	Price
MediaStream1Client	Test1	Book1	1.00
MediaStream10Client	Test10	Book10	10.00
MediaStream100Client	Test100	Book100	100.00

Answered By - AlwaysLearning

Answer Checked By - Cary Denson (WPSolving Admin)