SQL Server FOR XML explicit error in generating same name nodes

Question

SQL Server FOR XML explicit error in generating same name nodes

78 Views Asked by Nimble Fungus At 19 April 2025 at 16:11

i am trying to generate below XML Structure using for XML Explicit

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="1">
    <Table>
        <Row>
            <Cell>
                <Data ss:Type="String">231548</Data>
            </Cell>
            <Cell>
                <Data ss:Type="String">2014-11-03T20:03:30</Data>
            </Cell>
        </Row>
    </Table>
</Worksheet>

I am able to generate

 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <Worksheet ss:Name="1">
    <Table>
      <Row>
        <Cell>
          <Data ss:Type="String">231548</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>

I am unable to generate the above Structure. The code i am using to generate the structure is as follows.

SELECT 
    Tag, 
    Parent,
    [Workbook!1!xmlns],
    [Workbook!1!xmlns:ss],
    [Worksheet!2!ss:Name],
    [Table!3!],
    [Row!4!],
    [Cell!5!],
    [Data!6!ss:Type],
    [Data!6!]
FROM ( 
SELECT top 5
    1 AS Tag,
    0 AS Parent,
    0 as SORT,
    'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns],
    'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns:ss],
    NULL AS [Worksheet!2!ss:Name],
    NULL AS [Table!3!],
    NULL AS [Row!4!],
    NULL AS [Cell!5!],
    NULL AS [Data!6!ss:Type],
    NULL AS [Data!6!]

UNION ALL
SELECT top 5
    2 AS Tag,
    1 AS Parent,
    AuditID * 100  as SORT,
    NULL ,
    NULL ,
    Null,
    NULL ,
    NULL ,
    NULL ,
    NULL,
    NULL 
From TempAudits
UNION ALL
SELECT top 5
    3 AS Tag,
    2 AS Parent,
    AuditID * 100+1  as SORT,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL,
    NULL 
From TempAudits
UNION ALL
SELECT top 5
    4 AS Tag,
    3 AS Parent,
    AuditID * 100+2  as SORT,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL,
    NULL 
    From TempAudits
UNION ALL
SELECT top 5
    5 AS Tag,
    4 AS Parent,
    AuditID * 100+3  as SORT,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
FROM TempAudits
UNION ALL
SELECT top 5
    6 AS Tag,
    5 AS Parent,
    AuditID * 100+4  as SORT,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    'String' AS TYPE,
    AuditID AS Data
FROM TempAudits) Aa
Order by Sort
FOR XML EXPLICIT

Can any one please help me. Any help will be greatly appreciated. I am using this code to generate an xml that can be opened in excel.

Original Q&A

There are 1 best solutions below

**DaveFoyf** · Answer 1

Assuming that you do want to use XML Explicit to produce this output, you could add two more tags for Cell and Data and two subsequent Union Queries.

Edit: I've added a 1 into the Worksheet!2!ss:Name column of tag 2 to ensure the output matches your requirement.

SELECT 
    Tag, 
    Parent,
    [Workbook!1!xmlns],
    [Workbook!1!xmlns:ss],
    [Worksheet!2!ss:Name],
    [Table!3!],
    [Row!4!],
    [Cell!5!],
    [Data!6!ss:Type],
    [Data!6!],
    [Cell!7!],
    [Data!8!ss:Type],
    [Data!8!]

FROM 
( 
    SELECT top 5
        1 AS Tag,
        0 AS Parent,
        0 as SORT,
        'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns],
        'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]

    UNION ALL
    SELECT top 5
        2 AS Tag,
        1 AS Parent,
        AuditID * 100  as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        1 AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    From TempAudits

    UNION ALL
    SELECT top 5
        3 AS Tag,
        2 AS Parent,
        AuditID * 100+1  as SORT,
         NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    From TempAudits

    UNION ALL
    SELECT top 5
        4 AS Tag,
        3 AS Parent,
        AuditID * 100+2  as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
        From TempAudits

    UNION ALL
    SELECT top 5
        5 AS Tag,
        4 AS Parent,
        AuditID * 100 + 3  as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    FROM TempAudits

    UNION ALL
    SELECT top 5
        6 AS Tag,
        5 AS Parent,
        AuditID * 100 + 4 as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        'String' AS [Data!6!ss:Type],
        AuditID AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    FROM 
    TempAudits

    UNION ALL
    SELECT top 5
       7 AS Tag,
        4 AS Parent,
        AuditID * 100 + 5 as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    FROM TempAudits

    UNION ALL
    SELECT top 5
        8 AS Tag,
        7 AS Parent,
        AuditID * 100 + 6 as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        'String' AS [Data!8!ss:Type],
        AuditDate AS [Data!8!]
    FROM 
    TempAudits 
) Aa
Order by Sort
FOR XML EXPLICIT

SQL Server FOR XML explicit error in generating same name nodes

There are 1 best solutions below

Related Questions in SQL

Related Questions in SQL-SERVER

Related Questions in T-SQL

Related Questions in FOR-XML-EXPLICIT

Trending Questions

Popular # Hahtags

Popular Questions