Generating XML file from SQL query

Question

I'm trying to generate a XML file from SQL query and I have a table that contains following columns:

ItemNumber, Price, DateFrom, DateTo

The code I use to generate the XML is:

SELECT
    ItemNumber AS '@ItemNumber',
    Price AS '@Price',
    DateFrom AS 'DateFrom',
    DateTo AS 'DateTo'
FROM 
    #tempXML
FOR XML PATH('Item')

what I expect to get is something like this:

<Item id="111">
    <ItemNumber>111</ItemNumber>
        <Price value="3000">
            <DateFrom>2018-01-02</DateFrom>
            <DateTo>2018-01-30</DateTo>
        </Price>
        <Price value="2500">
            <DateFrom>2018-01-31</DateFrom>
            <DateTo>2018-11-22</DateTo>
        </Price>
 </Item>
 <Item>
    <ItemNumber>120</ItemNumber>
        <Price value="4000">
            <DateFrom>2018-01-12</DateFrom>
            <DateTo>2018-11-22</DateTo>
        </Price>
 </Item>

but instead I get something more like this:

<Item ItemNumber="111" Price="3000">
    <DateFrom>2018-01-02</DateFrom>
    <DateTo>2018-01-30</DateTo>
</Item>
<Item ItemNumber="111" Price="2500">
    <DateFrom>2018-01-31</DateFrom>
    <DateTo>2018-11-22</DateTo>
</Item>
<Item ItemNumber="120" Price="4000">
    <DateFrom>2018-01-12</DateFrom>
    <DateTo>2018-11-22</DateTo>
</Item>

Any kind of help will be greatly appreciated.

Sample data from the table I use

CREATE TABLE #tempXML
(
    ItemNumber INT,
    Price INT,
    DateFrom DATE,
    DateTo DATE
)

INSERT INTO #tempXML
VALUES
    (111, 3000, '2018-01-02', '2018-01-30'),
    (111, 2500, '2018-01-31', '2018-11-22'),
    (120, 4000, '2018-01-12', '2018-11-22')

SQL Fiddle: http://sqlfiddle.com/#!18/a0602/1

Where is ItemNumber coming from? That's not in your table's definition that you provide. Could you also provide sample data that isn't xml (i.e. is in the format your data is actually currently in). thanks. — Thom A
– Thom A ♦, Commented Nov 22, 2018 at 16:56
ItemNumber is a column in the #tempXML table, updated the question. Also added the data sample screen @Larnu — mjohansen
– mjohansen, Commented Nov 22, 2018 at 17:13
That's a weblink to an image, please supply the sample data as text, or (even better), as DDL and DML statements. — Thom A
– Thom A ♦, Commented Nov 22, 2018 at 17:14
great, thanks. this gives us something much better to work with. :) — Thom A
– Thom A ♦, Commented Nov 22, 2018 at 18:24

cpt_dood · Accepted Answer · 2018-11-22 17:41:07Z

2

You can specify how elements should be nested like this:

SELECT
ItemNumber AS '@id',
ItemNumber AS 'ItemNumber',
Price AS 'Price/@value',
DateFrom AS 'Price/DateFrom',
DateTo AS 'Price/DateTo'
FROM 
#tempXML
FOR XML PATH('Item');

see https://learn.microsoft.com/en-us/sql/relational-databases/xml/columns-with-a-name?view=sql-server-2017

edited Nov 22, 2018 at 17:41

answered Nov 22, 2018 at 17:14

cpt_dood

411 silver badge6 bronze badges

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

1 Comment

Thom A Over a year ago

This is close, but isn't quite what the OP is after. If you look, you'll notice the OP only has one node for Item id="111", which contains multiple Price nodes. This will produce 2 Item id="111" nodes, each with it own nodes for Price.

Thom A · Accepted Answer · 2018-11-22 18:32:47Z

This appears to be what you are after, however, it is a little messy:

SELECT T.ItemNumber AS [@ID],
       (SELECT T.ItemNumber,
               (SELECT sq.Price AS [@value],
                       (SELECT sq.DateFrom,
                               sq.DateTo
                        FOR XML PATH(''),TYPE)
                FROM #tempXML sq
                WHERE sq.ItemNumber = T.ItemNumber
                FOR XML PATH('Price'),TYPE)
        FOR XML PATH(''),TYPE)
FROM #tempXML T
GROUP BY ItemNumber
FOR XML PATH ('Item');

This results in:

<Item ID="111">
    <ItemNumber>111</ItemNumber>
    <Price value="3000">
        <DateFrom>2018-01-02</DateFrom>
        <DateTo>2018-01-30</DateTo>
    </Price>
    <Price value="2500">
        <DateFrom>2018-01-31</DateFrom>
        <DateTo>2018-11-22</DateTo>
    </Price>
</Item>
<Item ID="120">
    <ItemNumber>120</ItemNumber>
    <Price value="4000">
        <DateFrom>2018-01-12</DateFrom>
        <DateTo>2018-11-22</DateTo>
    </Price>
</Item>

Gottfried Lesigang · Accepted Answer · 2018-11-23 07:28:51Z

0

And one more suggestion, a bit simpler...

SELECT t1.ItemNumber AS [@id]
      ,t1.ItemNumber
      ,(
        SELECT t2.Price AS [@value]
              ,t2.DateFrom
              ,t2.DateTo
        FROM #tempXML t2
        WHERE t1.ItemNumber=t2.ItemNumber
        FOR XML PATH('Price'),TYPE
       )

FROM #tempXML t1
GROUP BY t1.ItemNumber
FOR XML PATH('Item');

Some explanantion:

The GROUP BY will reduce the outer SELECT to one-row-per-ItemNumber, while the sub-select will act as a correlated sub-query and fetch all price-details for the given Item.

answered Nov 23, 2018 at 7:28

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

Generating XML file from SQL query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related