locked
Renaming Elements in XML RRS feed

  • Question

  • Hi 

    i am trying to rename the following xml elements from 

    <tr>
      <column1>1</column1>
      <column2>2</column2>
    </tr>
    <tr>
      <column1>2</column1>
      <column2>3</column2>
    </tr>
    <tr>
      <column1>4</column1>
      <column2>5</column2>
    </tr>

    to

    <tr>
      <td>1</td>
      <td>2</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
    </tr>

    all solutions show to do so based on the node name however in this case i won't know what the name of the node would be and i may have multiple columns 



    eddy.a

    • Moved by Kalman Toth Wednesday, December 18, 2013 2:33 PM Better fit
    Tuesday, December 17, 2013 9:46 PM

Answers

  • My question is that it's easy to generate the file in that format from the start than rename elements. For the former check this tip

    http://beyondrelational.com/modules/1/justlearned/tips/8542/output-table-as-html-table.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Wednesday, December 18, 2013 12:09 AM
    • Marked as answer by Allen Li - MSFT Thursday, December 19, 2013 5:42 AM
    Wednesday, December 18, 2013 12:09 AM
  • yes!!!!!!! that kinda did the trick i am reposting the code here thank you very much !

    -- Created by Brad Schulz
    
    declare @Table sysname
    set @Table='tblTest'  --Insert your tablename here
    
    
    declare @sql nvarchar(max)
    
    ;with DesiredColumns as
    (
      select ColSeq=c.column_id
            ,ColName=c.name
            ,TypeName=t.name
            ,TypeAlign=case
                         when t.name in ('bigint','int','tinyint','numeric','decimal','float','money','smallmoney')
                         then N'right'
                         else N'left'
                       end
            ,ConvertCode=case
                           when t.name in ('datetime','smalldatetime','datetime2')
                           then N'121'
                           when t.name in ('money','smallmoney')
                           then N'2'
                           else N'0'
                         end
      from sys.columns c
      join sys.types t on c.user_type_id=t.user_type_id
      where c.object_id=object_id(@Table)
        and t.name not in ('timestamp','image','sql_variant','xml')
    )
    select @sql=N'select ''<table border=1>'' '
               +N'union all '
               +N'select (select '+stuff((select N',[*]='''''
                                                +N',[th/@align]='''+TypeAlign+''''
                                                +N',[th]='''+ColName+''''
                                          from DesiredColumns
                                          order by ColSeq
                                          for xml path(''),type).value('(./text())[1]','nvarchar(max)')
                                         ,1,8,N'')
               +N'        for xml path(''tr'')) '
               +N'union all '                  
               +N'select (select '+stuff((select N',[*]='''''
                                                +N',[td/@align]='''+TypeAlign+''''
                                                +N',[td]=convert(nvarchar(255),'
                                                                +QUOTENAME(ColName)+N','
                                                                +ConvertCode+N')'
                                          from DesiredColumns
                                          order by ColSeq
                                          for xml path(''),type).value('(./text())[1]','nvarchar(max)')
                                         ,1,8,N'')
               +N'        for xml path(''tr'')) '
               +N'from '+@Table+' '
               +N'union all '
               +N'select ''</table>'' '
    select @sql
    exec (@sql)
    


    eddy.a

    Wednesday, December 18, 2013 6:18 PM

All replies

  • Do you start from that XML? Do you need to use SQL Server for that?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, December 17, 2013 11:04 PM
  • Hi Yep it's all with SQL server and I need to use SQL server

    eddy.a

    Tuesday, December 17, 2013 11:53 PM
  • My question is that it's easy to generate the file in that format from the start than rename elements. For the former check this tip

    http://beyondrelational.com/modules/1/justlearned/tips/8542/output-table-as-html-table.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Wednesday, December 18, 2013 12:09 AM
    • Marked as answer by Allen Li - MSFT Thursday, December 19, 2013 5:42 AM
    Wednesday, December 18, 2013 12:09 AM
  • Understood and yea a file would be one way to solve this however I am using FOR XML to pull the data from a table - so the XML formatted here is select * from @table for XML raw('tr')

    eddy.a

    Wednesday, December 18, 2013 12:38 PM
  • Here's the illustration for your scenario

    --this corresponds to your actual table
    declare @x table
    (
    Column1 int,
    Column2 int
    )
    insert @x values (1,2),(2,3),(4,5)
    ;With cte
    as
    (
    select td ,seq
    from (select row_number() over (order by column1,column2) as seq,* from  @x )t
    cross apply(values(column1),(column2)) t1(td)
    )
    select 
    (select td from cte where seq=t.seq for xml path(''),type) as [*]
    from (select distinct seq from cte)t
    for xml path('tr')

    Now the output

    <tr>
      <td>1</td>
      <td>2</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
    </tr>



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, December 18, 2013 2:05 PM
  • I am moving it to XML.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, December 18, 2013 2:32 PM
  • thank you for your input

    another way is to use aliases

    for example

    select column1 as [td], column2 as [td] from @table for xml raw('tr'), ELEMENTS

    In this case scenario though we don't know how many columns we have so we can't specify the names in the select statement.

    I was wondering if there's a way to instead

    declare @xml xml;

    set @xml = (select column1 as [td], column2 as [td] from @table for xml raw('tr'), ELEMENTS)

    then use xquery to modify the node names

    at the end of the day i am trying to render a table content in SQL to HTML  and i am using XML as a method since there's no native way.


    eddy.a

    Wednesday, December 18, 2013 3:04 PM
  • Did you check the tip I posted? It should work for a table of any structure.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, December 18, 2013 4:37 PM
  • yes!!!!!!! that kinda did the trick i am reposting the code here thank you very much !

    -- Created by Brad Schulz
    
    declare @Table sysname
    set @Table='tblTest'  --Insert your tablename here
    
    
    declare @sql nvarchar(max)
    
    ;with DesiredColumns as
    (
      select ColSeq=c.column_id
            ,ColName=c.name
            ,TypeName=t.name
            ,TypeAlign=case
                         when t.name in ('bigint','int','tinyint','numeric','decimal','float','money','smallmoney')
                         then N'right'
                         else N'left'
                       end
            ,ConvertCode=case
                           when t.name in ('datetime','smalldatetime','datetime2')
                           then N'121'
                           when t.name in ('money','smallmoney')
                           then N'2'
                           else N'0'
                         end
      from sys.columns c
      join sys.types t on c.user_type_id=t.user_type_id
      where c.object_id=object_id(@Table)
        and t.name not in ('timestamp','image','sql_variant','xml')
    )
    select @sql=N'select ''<table border=1>'' '
               +N'union all '
               +N'select (select '+stuff((select N',[*]='''''
                                                +N',[th/@align]='''+TypeAlign+''''
                                                +N',[th]='''+ColName+''''
                                          from DesiredColumns
                                          order by ColSeq
                                          for xml path(''),type).value('(./text())[1]','nvarchar(max)')
                                         ,1,8,N'')
               +N'        for xml path(''tr'')) '
               +N'union all '                  
               +N'select (select '+stuff((select N',[*]='''''
                                                +N',[td/@align]='''+TypeAlign+''''
                                                +N',[td]=convert(nvarchar(255),'
                                                                +QUOTENAME(ColName)+N','
                                                                +ConvertCode+N')'
                                          from DesiredColumns
                                          order by ColSeq
                                          for xml path(''),type).value('(./text())[1]','nvarchar(max)')
                                         ,1,8,N'')
               +N'        for xml path(''tr'')) '
               +N'from '+@Table+' '
               +N'union all '
               +N'select ''</table>'' '
    select @sql
    exec (@sql)
    


    eddy.a

    Wednesday, December 18, 2013 6:18 PM
  • Here's a simpler way to do that using XQuery:

    DECLARE @table TABLE ( column1 INT, column2 INT )
    
    INSERT INTO @table
    VALUES	( 1, 2 ), ( 3, 4 ), ( 5, 6 )
    
    -- Create the xml
    DECLARE @xml XML 
    
    SET @xml = (
    	SELECT * FROM @table FOR XML RAW('tr'), ELEMENTS, TYPE
    	)
    
    SELECT @xml before
    
    SELECT @xml.query('
    for $e in tr
    	return <tr>{(for $l in $e/* return 
    		<td>{data($l)}</td>)
    }</tr>
    ') [after]

    • Proposed as answer by Naomi N Thursday, December 26, 2013 7:16 PM
    Thursday, December 26, 2013 7:10 PM
    Answerer
  • thanks Naomi this works great and is fast - i was wondering if you had a way to extract and insert the column names at first . I tried to search for some solution online but i could not find anthing helpful for variable tables.

    the end solution here is to format a table to HTML

    thanks


    eddy.a

    Tuesday, January 14, 2014 1:37 PM
  • you can use INFORMATION_SCHEMA.COLUMNS table to get column names for the table

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Naomi N Tuesday, January 14, 2014 3:55 PM
    Tuesday, January 14, 2014 2:09 PM