Using SQL to Generate XML

Question

I'm trying to use SQL to generate XML in the format:

<ImportSession>
  <Batches>
    <Batch>
      <BatchFields>
        <BatchField Name="Field1" Value="1" />
        <BatchField Name="Field2" Value="2" />
        <BatchField Name="Field3" Value="3" />
      </BatchFields>
    <Batch>
  <Batches>
</ImportSession>

I'm using SQL Server 2008. I wrote this query:

SELECT
    (SELECT
         (SELECT 
              'Col' AS [@Name],
              FiscalYear AS [@Value]
          FROM [ICEM].[dbo].[ExportedBill]
          WHERE ExportedBillID = 1
          FOR XML PATH ('BatchField'), TYPE)
     FROM [ICEM].[dbo].[ExportedBill]
     WHERE ExportedBillID = 1
     FOR XML PATH ('BatchFields'), ROOT ('Batch'), TYPE)
FROM
    [ICEM].[dbo].[ExportedBill]
WHERE
    ExportedBillID = 1
FOR XML PATH ('Batches'), ROOT ('ImportSession')

And this results in:

<ImportSession>
  <Batches>
    <Batch>
      <BatchFields>
        <BatchField Name="Col" Value="2015" />
      </BatchFields>
    </Batch>
  </Batches>
</ImportSession>

What I need though is every column should have an entry in BatchField. Also I need the column name to show up in the name. So I should get:

<BatchField Name="FiscalYear" Value="2015" />
<BatchField Name="MeterNumber" Value="123456" />
<BatchField Name="Name" Value="John Smith" />
<BatchField Name="Utility" Value="Electricity" />

So can anyone tell me how I modify my query to get what I need?

EDIT:

I figured it out. I needed a second nested Select. I need one for each column. If they proceeding selects use the same tags as a previous Select then the information is concatanated under the same parent tag

SELECT
    (SELECT
         (SELECT 
              'FiscalYear' AS [@Name],
              FiscalYear AS [@Value]
          FROM [ICEM].[dbo].[ExportedBill]
          WHERE ExportedBillID = 1
          FOR XML PATH ('BatchField'), TYPE),
          (SELECT 'FiscalPeriod' AS [@Name],
            FiscalPeriod AS [@Value]
          FROM [PEEL_ICEM].[dbo].[ExportedBill]
          WHERE ExportedBillID = 1
          FOR XML PATH ('BatchField'), TYPE)
     FROM [ICEM].[dbo].[ExportedBill]
     WHERE ExportedBillID = 1
     FOR XML PATH ('BatchFields'), ROOT ('Batch'), TYPE)
FROM
    [ICEM].[dbo].[ExportedBill]
WHERE
    ExportedBillID = 1
FOR XML PATH ('Batches'), ROOT ('ImportSession')

Thing is though, there will be around 70 columns in this table. Ill brute force it for now, but if anyone knows of a better way to do this please let me know. Cheers

GarethD · Accepted Answer · 2015-10-06 16:30:02Z

1

You can create separate child elements by adding a blank column separator. e.g.

DECLARE @T TABLE 
(   FiscalYear INT, 
    MeterNumber INT, 
    Name VARCHAR(255), 
    Utility VARCHAR(255)
);
INSERT @T VALUES (2015, 123456, 'John Smith', 'Electricity');

SELECT  [BatchField/@Name] = 'FiscalYear',
        [BatchField/@Value] = FiscalYear, 
        '',
        [BatchField/@Name] = 'MeterNumber',
        [BatchField/@Value] = MeterNumber,
        '',
        [BatchField/@Name] = 'Name',
        [BatchField/@Value] = Name,
        '',
        [BatchField/@Name] = 'Utility',
        [BatchField/@Value] = Utility
FROM    @T
FOR XML PATH('BatchFields'), ROOT('Batch');

Which gives:

<Batch>
  <BatchFields>
    <BatchField Name="FiscalYear" Value="2015" />
    <BatchField Name="MeterNumber" Value="123456" />
    <BatchField Name="Name" Value="John Smith" />
    <BatchField Name="Utility" Value="Electricity" />
  </BatchFields>
</Batch>

answered Oct 6, 2015 at 16:30

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

discodowney Over a year ago

Cheers man. That helps a lot. I wasnt looking forward to the state my query was gonna be in if I had done it my way.

Collectives™ on Stack Overflow

Using SQL to Generate XML

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related