CODEWITHSUNDEEP
sqlsql-serverxml
cmcapellan
cmcapellan

Reputation: 355

SQL Server FOR XML PATH vary tags dynamically

Trying to use FOR XML in SQL 2012. Need to have a result like this:

<Loader xmlns:xsi="url1" xmlns="url2">
  <Buy_New>
    <Ticker>IBM</Ticker>
    <Acct>12345</Acct>
    <Qty>10</Qty>
  </Buy_New>
  <Sell_New>
    <Ticker>MSFT</Ticker>
    <Acct>12345</Acct>
    <Qty>15</Qty>
  </Sell_New>
  <Buy_New>
    <Ticker>IBM</Ticker>
    <Acct>12345</Acct>
    <Qty>10</Qty>
  </Buy_New>
</Loader>

After looking on here, MSDN, etc., I am not seeing a way to dynamically change the <Buy_New> and <Sell_New> (I have about 20 different types to work with) in my query, I have explored using FOR XML PATH and FOR XML EXPLICIT but both seem to require static element tags.

Is there a way to drive the tags off the rows in the query?

@Jon C Sure: here is what I have:

SELECT
(
    SELECT
    Investment1 as Investment,
    EventDate1 as Date,
    Quantity1 as Quantity
    FROM #temptable e1 where e1.temp_id = e.temp_id
    FOR XML PATH(''),TYPE
) AS 'DYNAMICTAG'
FROM #temptable e
FOR XML PATH(''), ROOT('Loader')`

Here is a sample of the result. The DYNAMICTAG is the part that needs to change to be <Buy_New>, <Sell_New> etc.

<Loader>
  <DYNAMICTAG>
    <Investment>XYZ</Investment>
    <Date>2015-05-08T00:00:00</Date>
    <Quantity>50</Quantity>
  </DYNAMICTAG>
  <DYNAMICTAG>
    <Investment>ABC</Investment>
    <Date>2015-05-08T00:00:00</Date>
    <Quantity>10</Quantity>
  </DYNAMICTAG>
  <DYNAMICTAG>
    <Investment>CSCO</Investment>
    <Date>2015-05-08T00:00:00</Date>
    <Quantity>50</Quantity>
  </DYNAMICTAG>
  <DYNAMICTAG>
    <Investment>IBM</Investment>
    <Date>2015-05-08T00:00:00</Date>
    <Quantity>30</Quantity>
  </DYNAMICTAG>
</Loader>

Upvotes: 0

Views: 2244

Answers (3)

cmcapellan
cmcapellan

Reputation: 355

Refined Answer (based on @JonC's suggestion to use CAST, but without needing a separate table to hold all the different possibilities for tags)

SELECT
    (
    SELECT 
        CAST('<' + t1.RecordType + '>' +
            CAST(
            (
                SELECT
                t2.Portfolio1 AS 'Portfolio',
                t2.Qty1 AS 'Qty',
                t2.Price AS 'Price',
                FROM #temptable t2 
                WHERE t2.temptable_id = t1.temptable_id
                FOR XML PATH(''),TYPE
            ) as varchar(max))
        + '</' + t1.RecordType + '>' as xml)
    from #temptable t1
    FOR XML PATH(''), TYPE 
    ) AS 'TranRecords'
FOR XML PATH(''), ROOT('Loader')

Upvotes: 5

Jon C
Jon C

Reputation: 664

I'm not sure about the condition to determine if the tag should be 'buy_new' or 'sell_new', but this may work for you:

    SELECT
(
    SELECT
    Investment1 as Investment,
    EventDate1 as Date,
    Quantity1 as Quantity
    FROM #temptable e1 where e1.temp_id = e.temp_id
    AND (SOME CONDITION FOR 'Buy_New')
    FOR XML PATH('Buy_New'),TYPE
) ,
(
    SELECT
    Investment1 as Investment,
    EventDate1 as Date,
    Quantity1 as Quantity
    FROM #temptable e1 where e1.temp_id = e.temp_id
    AND (SOME CONDITION FOR 'Sell_New')
    FOR XML PATH('Sell_New'),TYPE
)
FROM #temptable e
FOR XML PATH(''), ROOT('Loader')
GO

EDIT:

By your answer I understand that you need something more dynamic. This may not be the elegant solution you're looking for, but It may get the job done without having to hard code every single element:

First, create a table to store your desired dynamic element names:

create table elements (id int, name nvarchar(20))

insert into elements (id, name) values (1, 'sell_new')
insert into elements (id, name) values (2, 'buy_new')
insert into elements (id, name) values (3, 'other_new')

Then comes the workaround:

SELECT cast('<' + e.name +'>' +
        cast(   
                (
                    SELECT
                    Investment AS 'Investment',
                    EventDate as 'Date',
                    Quantity AS 'Quantity'
                    FROM temptable t1
                    WHERE t1.investment = t.investment
                    FOR XML PATH(''),TYPE
                ) as varchar(max)) 
    + '</' + e.name +'>' as xml)
from temptable t 
JOIN elements e ON e.id = t.RecordType
order by investment
for xml path(''), root('Loader')

The result:

<Loader>
  <sell_new>
    <Investment>abc</Investment>
    <Quantity>456</Quantity>
  </sell_new>
  <buy_new>
    <Investment>cde</Investment>
    <Quantity>789</Quantity>
  </buy_new>
  <sell_new>
    <Investment>efg</Investment>
    <Quantity>0</Quantity>
  </sell_new>
</Loader>

Upvotes: 32

cmcapellan
cmcapellan

Reputation: 355

There might be a more elegant solution (and I'm hoping someone else posts it!) but I think Jacob's Blog has a workable solution here:

FOR XML PATH – Yet another shaping example using FOR XML PATH

Code example is here:

SELECT
    (
        SELECT
        Investment AS 'Investment',
        Quantity AS 'Quantity',
        Price AS 'Price'
        FROM #temptable
        WHERE RecordType = 'Buy'
        FOR XML PATH('Buy_New'),TYPE
    ) AS 'TransactionRecords',
    (
        SELECT
        Investment AS 'Investment',
        Quantity AS 'Quantity',
        Price AS 'Price'
        FROM #temptable
        WHERE RecordType = 'Sell'
        FOR XML PATH('Sell_New'),TYPE
    ) AS 'TransactionRecords',
    (
        SELECT
        Investment AS 'Investment',
        Quantity AS 'Quantity',
        Price AS 'Price'
        FROM #temptable
        WHERE RecordType = 'Short'
        FOR XML PATH('Short_New'),TYPE
    ) AS 'TransactionRecords',
    (
        SELECT
        Investment AS 'Investment',
        Quantity AS 'Quantity',
        Price AS 'Price'
        FROM #temptable
        WHERE RecordType = 'Cover'
        FOR XML PATH('Cover_New'),TYPE
    ) AS 'TransactionRecords'
    FOR XML PATH(''), ROOT('GenevaLoader')

Upvotes: 1

Related Questions