Htykuut

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')

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://docs.microsoft.com/en-us/sql/relational-databases/xml/columns-with-a-name?view=sql-server-2017

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>

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.