Create XML using TSQL

Question

I am trying to create the following XML document and am having trouble understanding how to do this using TSQL. I have done a lot of research and believe I can do this using XML Path instead of XML Explicit. Any help trying to create the below output would be greatly appreciated.

Sample Dataset:

CREATE TABLE #DataTable
(
PortfolioShortName varchar(20)
,PortfolioLongName varchar(255)
,BenchmarkName varchar(255)
,BenchmarkCode varchar(255)
,PositionGroupName varchar(255)
)
INSERT INTO #DataTable (PortfolioShortName,PortfolioLongName,BenchmarkName,BenchmarkCode,PositionGroupName)
VALUES ('TESTIG','Long Division Short Duration','SP 500','SP.MC.SP500@rmgBenchmarks','Test Strategy'),
('TESTEM','Short Duration Emerging Market','Dow','Dow.MC.Dow@rmgBenchmarks','Test Strategy')

SELECT
*
FROM #DataTable dt

XML I am trying to create:

<?xml version="1.0" encoding="UTF-8"?>
<positionGroupMetadata xmlns="urn:TestingData-types" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:TestingData:1.0:positiongroupmetadata-types file:///C:/Users/BillBill/Desktop/Schemas/positiongroupmetadata-types.xsd">
    <setIdentification>
        <client>Georgia Capital</client>
        <name>Testing Service</name>
        <date>20150924</date>
    </setIdentification>
    <positionGroupItemList>
        <positionGroupItem>
            <positionGroupID type="client">
                <positionGroupValue>TESTIG</positionGroupValue>
            </positionGroupID>
            <tagGroupList>
                <tagGroup>
                    <name>Benchmarks</name>
                    <tagList>
                        <tag>
                            <type>defaultBenchmarkName</type>
                            <value>Long Division Short Duration</value>
                        </tag>
                        <tag>
                            <type>defaultBenchmarkPrimaryIdentifier</type>
                            <value>SP.MC.SP500@rmgBenchmarks</value>
                        </tag>
                    </tagList>
                </tagGroup>
            </tagGroupList>
        </positionGroupItem>
    </positionGroupItemList>
    <positionGroupByWeightList>
        <positionGroupByWeight>
            <positionGroupName>Test Strategy</positionGroupName>
            <positionList>
                <position>
                    <positionGroupID type="client">
                        <positionGroupValue>TESTIG</positionGroupValue>
                    </positionGroupID>
                </position>
                <position>
                    <positionGroupID type="client">
                        <positionGroupValue>TESTEM</positionGroupValue>
                    </positionGroupID>
                </position>
            </positionList>
        </positionGroupByWeight>
    </positionGroupByWeightList>
    <reportSetList>
        <reportSet>
            <reportSetName>DailyReports</reportSetName>
            <reportDataSetList>
                <reportDataSet>
                    <reportDataSetName>TESTIG</reportDataSetName>
                    <positionGroup>
                        <positionGroupIDList>
                            <positionGroupID type="client">
                                <positionGroupValue>TESTIG</positionGroupValue>
                                <holdingGroupAlias>Portfolio</holdingGroupAlias>
                            </positionGroupID>
                        </positionGroupIDList>
                    </positionGroup>
                </reportDataSet>
                <reportDataSet>
                    <reportDataSetName>TESTIG</reportDataSetName>
                    <positionGroup>
                        <positionGroupIDList>
                            <positionGroupID type="client">
                                <positionGroupValue>TESTIG</positionGroupValue>
                                <holdingGroupAlias>Portfolio</holdingGroupAlias>
                            </positionGroupID>
                        </positionGroupIDList>
                    </positionGroup>
                </reportDataSet>
            </reportDataSetList>
        </reportSet>
    </reportSetList>
</positionGroupMetadata>

This is as far as I could get. I am not sure how to build the upper list and work my way up the structure.

SELECT DISTINCT
        type = 'defaultBenchmarkPrimaryIdentifier'
        ,value = dt.BenchmarkName
        FROM #DataTable dt 

        UNION ALL

        SELECT
        type = 'defaultBenchmarkPrimaryIdentifier'
        ,dt.BenchmarkCode AS defaultBenchmarkPrimaryIdentifier
        FROM #DataTable dt 
        FOR XML PATH('tag'),ROOT('type');

This is where I am. SELECT DISTINCT type = 'defaultBenchmarkPrimaryIdentifier' ,value = dt.BenchmarkName FROM #DataTable dt UNION ALL SELECT type = 'defaultBenchmarkPrimaryIdentifier' ,dt.BenchmarkCode AS defaultBenchmarkPrimaryIdentifier FROM #DataTable dt FOR XML PATH('tag'),ROOT('type'); — Alex, Sep 24 '15 at 15:20
Checkout this answer, may well help http://stackoverflow.com/a/14778673/1370442 — Luke Baughan, Sep 24 '15 at 15:30

score 0 · Accepted Answer · answered Sep 25 '15 at 07:44

You did not give enough information... And please do the hard work yourself. The following is a beginning which will help you to understand the abilities of FOR XML PATH.

The output is not yet complete (how could it be...) and it is not yet exactly what you posted as needed. But you will be able to adapt it and come back with clear questions upon details you cannot solve on your own.

One point to state immediately: The repetition of the namespaces in nested XML cannot be surpressed with SQL Server 2012 (AFAIK). You could go on without namespaces and fill them in after the create process with string functions...

Good luck!

CREATE TABLE #DataTable
(
PortfolioShortName varchar(20)
,PortfolioLongName varchar(255)
,BenchmarkName varchar(255)
,BenchmarkCode varchar(255)
,PositionGroupName varchar(255)
)
INSERT INTO #DataTable (PortfolioShortName,PortfolioLongName,BenchmarkName,BenchmarkCode,PositionGroupName)
VALUES ('TESTIG','Long Division Short Duration','SP 500','SP.MC.SP500@rmgBenchmarks','Test Strategy'),
('TESTEM','Short Duration Emerging Market','Dow','Dow.MC.Dow@rmgBenchmarks','Test Strategy')

SELECT
*
FROM #DataTable dt;

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS [xsi]
                  ,'urn:TestingData:1.0:positiongroupmetadata-types file:///C:/Users/BillBill/Desktop/Schemas/positiongroupmetadata-types.xsd' AS [schemaLocation]
                  ,DEFAULT 'urn:TestingData-types')
SELECT 'Georgia Capital' AS [setIdentification/client]
      ,'Testing Service' AS [setIdentification/name]
      ,CONVERT(VARCHAR(8),GETDATE(),112) AS [setIdentification/date]
      ,node1.positionGroupItemList
FROM (SELECT NULL AS dummy) AS dummy
CROSS APPLY
(
    SELECT 'client' AS [positionGroupID/@type]
          ,PortfolioShortName  AS [positionGroupID/positionGroupValue]
          ,'Benchmarks' AS [tagGroupList/tagGroup/name]
          ,'defaultBenchmarkName' AS [tagGroupList/tagGroup/tagList/tag/type]
          ,PortfolioLongName  AS [tagGroupList/tagGroup/tagList/tag/value]
          ,'defaultBenchmarPrimaryIdentifier' AS [tagGroupList/tagGroup/tagList/tag/type]
          ,BenchmarkCode  AS [tagGroupList/tagGroup/tagList/tag/value]
    FROM #DataTable
    FOR XML PATH('positionGroupItem'),TYPE
) AS node1(positionGroupItemList)
FOR XML PATH(''),ROOT('positionGroupMetadata')

GO
DROP TABLE #DataTable

Thank you for the help. This is great start to understand how to nest the data. — Alex, Sep 25 '15 at 23:56

score 0 · Answer 2 · answered Sep 26 '15 at 00:09

I went with a very unique approach using strings / cursors to manipulate the data into the format I needed. I worked my way down the structure adding more and more strings together for each element until I had the entire XML document.

Thanks for the hep stack overflow.

DECLARE @EndText varchar(MAX)

SELECT @StartText = '<positionGroupByWeight>
            <positionGroupName>' + @Portfolio + '</positionGroupName>
            <positionList>'

SELECT @EndText = '</positionList>
            </positionGroupByWeight>'

SET @XML = CONVERT(varchar(max),ISNULL(@StartText,'')) + '' + CONVERT(varchar(max),@EndText)

SELECT @XML

Create XML using TSQL

2 Answers2