Search code examples
sql-serverxmlt-sqlxquery

SQL Server - output of XML node structure as per data

Requesting help for this: I have a requirement where the xml output needs to be in below desired format. The main hurdle is to get dynamic xml node <Shares1_1> which is Keyword 'Shares' concatenated with data in column Shares and ID.

Desired output:

enter image description here

Current table structure and data:

DROP TABLE IF EXISTS #TMP
CREATE TABLE #TMP(ID smallint, Shares VARCHAR(50), Name VARCHAR(50))

INSERT INTO #TMP(ID, Shares, Name) 
VALUES (1,1,'John'), (2,1,'Tim'), (3,2,'Miles')

select * from #tmp

My attempt to get the output (not correct)

declare @xml xml 
select @xml =(
    
        select ID, Shares, Name
        from #tmp
        for xml path ('snap'),elements, xsinil, type
)
select @xml

Thanking you all in advance for looking into this.

Cheers.

Solution

  • SQL Server is declarative by design. You would have to use Dynamic SQL or a little string manipulation.

    Example

    Declare @S varchar(max) = ''

Select @S=@S+concat('<Shares',Shares,'_',ID,'>',B.XML,'</Shares',Shares,'_',ID,'>')
 From #tmp A
 Cross Apply ( Select A.* for XML path('') ) B(xml)

Declare @XML xml = convert(xml,concat('<Snap>',@S,'</Snap>'))
Select @XML

    Results

    <Snap>
  <Shares1_1>
    <ID>1</ID>
    <Shares>1</Shares>
    <Name>John</Name>
  </Shares1_1>
  <Shares1_2>
    <ID>2</ID>
    <Shares>1</Shares>
    <Name>Tim</Name>
  </Shares1_2>
  <Shares2_3>
    <ID>3</ID>
    <Shares>2</Shares>
    <Name>Miles</Name>
  </Shares2_3>
</Snap>