Unable to generate FOR XML

Question

I am stuck with FOR XML. I am using SQL Server 2008.

I am trying to generate a XML using FOR XML. Please see the sample data at http://sqlfiddle.com/#!9/4e180e

I want to transform the data into below XML format. I want to convert the labels into label <Headers> and data into data tags. In my actual scenario the number of columns in table are dynamic.

I would request if you can suggest a dynamic way to generate the XML where number of columns should not effect the logic of XML generation.

<RootNode>
    <Subject>
        </SubjectID=94>
        <FORMName>
            <Headers>
                <Header>VISIT</Header>
            </Headers>
            <Datas>
                <Data>1<Data>
            </Datas> 
            <Headers>
                <Header>Date</Header>
            </Headers>
            <Datas>
                <Data>8 Aug<Data>
            </Datas>
            <Headers>
                <Header>Doc Name</Header><Header>Hostipal Name</Header>
            </Headers>
            <Datas>
                <Data>Dr Sam</Data><Data>Happy Memorial</Data>
            </Datas>
            <Datas>
                <Data>Dr Sam</Data><Data>Happy Memorial</Data>
            </Datas>
        </FORMName>
    </Subject>
<RootNode>

I am stuck at very initial level and not able to move forward.

Kindly help me.

Answer 1

This structure is - uhm - weird...

Your table is sort of Key-Value-Pair with a 1:n-dependency of Headers and Datas. This violates several rules of normalization...

Your XML will be queryable only by the corresponding positions of a Header and its Data. Just imagine some Data as NULL, you'll have to handle this as well...

Btw: Your </SubjectID=94> is not valid...

If you are the owner of this structure, you should rather think about How can I improve this structure?

Nevertheless this can be done - although I wouldn't:

CREATE TABLE DataCols (
ID INT NOT NULL IDENTITY PRIMARY KEY, 
SubjectID INT, 
FormName VARCHAR(100),
ItemDetail VARCHAR(100),
POSITION INT,
col1 VARCHAR(255),
col2 VARCHAR(255)
);

INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(94,'TOX','Label',0,'Visit');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(94,'TOX','Data',0,'1');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(94,'TOX','Label',0,'Date');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(94,'TOX','Date',0,'8 Aug');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1,col2) VALUES(94,'TOX','Label',1,'Doc Name','Hostipal Name');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1,col2) VALUES(94,'TOX','Data',1,'Dr Sam','Happy Memorial');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1,col2) VALUES(94,'TOX','Data',2,'Dr Sam','Happy Memorial');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(98,'TOX','Label',0,'Visit');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(98,'TOX','Data',0,'1');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(98,'TOX','Label',0,'Date');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1)      VALUES(98,'TOX','Date',0,'4 Jan');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1,col2) VALUES(98,'TOX','Label',1,'Doc Name','Hostipal Name');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1,col2) VALUES(98,'TOX','Data',1,'Dr Sam','Vegas Hostipal');
INSERT INTO DataCols (SubjectID,FormName,ItemDetail,POSITION,col1,col2) VALUES(98,'TOX','Data',2,'Dr Sam','Vegas Hostipal');
GO

--And here's the query:

WITH DistinctID AS
(SELECT DISTINCT SubjectID FROM DataCols)
,Labels AS
(
    SELECT SubjectID
          ,ID
          ,(SELECT col1 AS Header,'',col2 AS Header FOR XML PATH('Headers'),TYPE) AS HeaderXML
    FROM DataCols AS c
    WHERE ItemDetail='Label'
)
,LabelsExt AS
(
    SELECT * 
          ,(SELECT MIN(x.ID) FROM Labels AS x WHERE x.ID>Labels.ID) AS NextID
    FROM Labels
)
SELECT SubjectID
      ,(
        SELECT HeaderXML AS [*]
              ,(SELECT col1 AS Data,'',col2 AS Data 
                FROM DataCols 
                WHERE DataCols.ID BETWEEN l.ID+1 AND ISNULL(l.NextID,999999)-1 FOR XML PATH('Datas'),TYPE)  AS [*]
        FROM LabelsExt AS l
        WHERE l.SubjectID=DistinctID.SubjectID
        FOR XML PATH(''),ROOT('FORMName'),TYPE
       )
FROM DistinctID
FOR XML PATH('Subject'),ROOT('RootNode')

--Clean-Up (Carefull if real data!)

GO
--DROP TABLE DataCols;

UPDATE

You might try this to get the header as attribute into your Data-element. In general I'd prefer to name the elements as what they are... Such a structure is only to prefer in case of dynamically created fields where you do not know the structure in advance.

WITH DistinctID AS
(SELECT DISTINCT SubjectID FROM DataCols)
,Labels AS
(
    SELECT SubjectID
          ,ID
          ,col1,col2
    FROM DataCols AS c
    WHERE ItemDetail='Label'
)
,LabelsExt AS
(
    SELECT * 
          ,(SELECT MIN(x.ID) FROM Labels AS x WHERE x.ID>Labels.ID) AS NextID
    FROM Labels
)
SELECT SubjectID
      ,(
        SELECT (SELECT l.col1 AS [Data/@header], c.col1 AS Data
                      ,''
                      ,l.col2 AS [Data/@header], c.col2 AS Data 
                FROM DataCols AS c 
                WHERE c.ID BETWEEN l.ID+1 AND ISNULL(l.NextID,999999)-1 
                FOR XML PATH('Datas'),TYPE)  AS [*]
        FROM LabelsExt AS l
        WHERE l.SubjectID=DistinctID.SubjectID
        FOR XML PATH(''),ROOT('FORMName'),TYPE
       )
FROM DistinctID
FOR XML PATH('Subject'),ROOT('RootNode')