SQL Server version:
Microsoft SQL Server 2016 (SP1-GDR) (KB4458842) - 13.0.4224.16 (X64)
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)
I have a need to build a nested XML file that has a top tag of monthlydata, then I need a single header present at the top of the file inside monthlydata. Finally, I have repeated fields nested inside the monthlydata but surrounded by the header.
Here is the table design:
CREATE TABLE [dbo].[f_XML_Data]
(
[SurveyID] [VARCHAR](15) NOT NULL,
[RecDate] [VARCHAR](15) NOT NULL,
[DisDate] [VARCHAR](15) NOT NULL,
[CMS_10] [VARCHAR](50) NULL,
[CMS_11] [VARCHAR](50) NULL,
[CMS_23] [VARCHAR](50) NULL,
[CMS_24] [VARCHAR](50) NULL,
[CMS_27] [VARCHAR](50) NULL,
[CMS_30] [VARCHAR](50) NULL,
[ITADMSRC] [VARCHAR](50) NULL,
[ITDCGSTA] [VARCHAR](50) NULL,
[ITDISDAT] [VARCHAR](50) NULL,
[ITHCAHPS] [VARCHAR](50) NULL,
[ITSERVIC] [VARCHAR](50) NULL
)
Here is the data:
INSERT INTO dbo.f_XML_Data VALUES ('6561942362','2024-07-10','2024-03-12','Always','Always','8','Definitely yes','Some college','English','1','6','2024-03-12','220090','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6705168282','2024-07-15','2024-04-10','Always','Always','9','Definitely yes','Some college','English','1','3','2024-04-10','220091','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6866086131','2024-07-02','2024-05-10','Always','Always','9','Definitely yes','4-yr coll. grad.','English','1','1','2024-05-10','220092','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6903690620','2024-07-02','2024-05-16','Usually','Always','9','Definitely yes','4-yr coll. grad.','English','1','1','2024-05-16','220093','Removal')
INSERT INTO dbo.f_XML_Data VALUES ('6978329252','2024-07-09','2024-05-21','Always','Always','10-Best possible','Definitely yes','Some college','English','1','1','2024-05-21','220094','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978332884','2024-07-09','2024-05-31','Sometimes','Usually','8','Definitely no','NULL','NULL','1','3','2024-05-31','220095','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978336149','2024-07-02','2024-05-21','Usually','Always','10-Best possible','Definitely yes','Some college','English','1','6','2024-05-21','220096','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978337433','2024-07-16','2024-06-01','Never','Always','6','Probably yes','4-yr coll. grad.','English','1','1','2024-06-01','220097','Removal')
INSERT INTO dbo.f_XML_Data VALUES ('6978344956','2024-07-09','2024-06-01','Always','Usually','8','Probably yes','Some college','NULL','1','3','2024-06-01','220098','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978347260','2024-07-12','2024-05-23','Usually','Usually','10-Best possible','Definitely yes','Some college','English','1','6','2024-05-23','220099','Storage')
Here is my current code:
SELECT
(SELECT
'Forward Design' AS [company-name],
'782837' AS [client-id],
main.[SurveyID] AS [SurveyID],
DATEPART(YEAR, [ITDISDAT]) AS [rsp-yr],
DATEPART(MONTH, [ITDISDAT]) AS [rsp-month]
FROM
dbo.f_XML_Data hdr
WHERE
hdr.[SurveyID] = main.[SurveyID]
FOR XML PATH('Header'), TYPE),
(SELECT
(SELECT
[ITHCAHPS] AS [surveysub-id],
DATEPART(YEAR, [ITDISDAT]) AS [rsp-yr],
DATEPART(MONTH, [ITDISDAT]) AS [rsp-month],
[ITADMSRC] AS [source],
[ITSERVIC] AS [principal-reason],
[ITDCGSTA] AS [status], -- Already translated for PG in the file we send
[CMS_30] AS [language],
DATEDIFF(DAY, CAST([DisDate] AS DATE), CAST([RecDate] AS DATE)) AS [lag-time],
'52' AS [supplemental-question-count]
FROM
dbo.f_XML_Data admin
WHERE
admin.[SurveyID] = main.[SurveyID]
FOR XML PATH(''), ROOT('administration'), TYPE),
(SELECT
[CMS_10] AS [cleanliness],
[CMS_11] AS [quiet],
[CMS_23] AS [overall-rate],
[CMS_24] AS [recommend],
[CMS_27] AS [education],
[CMS_30] AS [language-speak]
FROM
dbo.f_XML_Data admin
WHERE
admin.[SurveyID] = main.[SurveyID]
FOR XML PATH(''), ROOT('response'), TYPE)
FOR XML PATH(''), ROOT('coredata'), TYPE)
FROM
dbo.f_XML_Data main
FOR XML PATH(''), ROOT('monthlydata'), TYPE
This is what I get (first two rows only for brevity):
<monthlydata>
<Header>
<company-name>Forward Design</company-name>
<client-id>782837</client-id>
<SurveyID>6561942362</SurveyID>
<rsp-yr>2024</rsp-yr>
<rsp-month>3</rsp-month>
</Header>
<coredata>
<administration>
<surveysub-id>220090</surveysub-id>
<rsp-yr>2024</rsp-yr>
<rsp-month>3</rsp-month>
<source>1</source>
<principal-reason>Storage</principal-reason>
<status>6</status>
<language>English</language>
<lag-time>120</lag-time>
<supplemental-question-count>52</supplemental-question-count>
</administration>
<response>
<cleanliness>Always</cleanliness>
<quiet>Always</quiet>
<overall-rate>8</overall-rate>
<recommend>Definitely yes</recommend>
<education>Some college</education>
<language-speak>English</language-speak>
</response>
</coredata>
<Header>
<company-name>Forward Design</company-name>
<client-id>782837</client-id>
<SurveyID>6561942362</SurveyID>
<rsp-yr>2024</rsp-yr>
<rsp-month>4</rsp-month>
</Header>
<coredata>
<administration>
<surveysub-id>220091</surveysub-id>
<rsp-yr>2024</rsp-yr>
<rsp-month>4</rsp-month>
<source>1</source>
<principal-reason>Storage</principal-reason>
<status>3</status>
<language>English</language>
<lag-time>96</lag-time>
<supplemental-question-count>52</supplemental-question-count>
</administration>
<response>
<cleanliness>Always</cleanliness>
<quiet>Always</quiet>
<overall-rate>9</overall-rate>
<recommend>Definitely yes</recommend>
<education>Some college</education>
<language-speak>English</language-speak>
</response>
</coredata>
</monthlydata>
This is what I would like to get(Again, first two rows only):
<monthlydata>
<Header>
<company-name>Forward Design</company-name>
<client-id>782837</client-id>
<SurveyID>6561942362</SurveyID>
<rsp-yr>2024</rsp-yr>
<rsp-month>3</rsp-month>
<coredata>
<administration>
<surveysub-id>220090</surveysub-id>
<rsp-yr>2024</rsp-yr>
<rsp-month>3</rsp-month>
<source>1</source>
<principal-reason>Storage</principal-reason>
<status>6</status>
<language>English</language>
<lag-time>120</lag-time>
<supplemental-question-count>52</supplemental-question-count>
</administration>
<response>
<cleanliness>Always</cleanliness>
<quiet>Always</quiet>
<overall-rate>8</overall-rate>
<recommend>Definitely yes</recommend>
<education>Some college</education>
<language-speak>English</language-speak>
</response>
</coredata>
<coredata>
<administration>
<surveysub-id>220091</surveysub-id>
<rsp-yr>2024</rsp-yr>
<rsp-month>4</rsp-month>
<source>1</source>
<principal-reason>Storage</principal-reason>
<status>3</status>
<language>English</language>
<lag-time>96</lag-time>
<supplemental-question-count>52</supplemental-question-count>
</administration>
<response>
<cleanliness>Always</cleanliness>
<quiet>Always</quiet>
<overall-rate>9</overall-rate>
<recommend>Definitely yes</recommend>
<education>Some college</education>
<language-speak>English</language-speak>
</response>
</coredata>
</Header>
</monthlydata>
Hope that is what you all are looking for. I really would like to avoid doing string concatenation if possible.
Please try the following solution.
I had to adjust sample data to allow grouping based on two columns: SurveyID and ITDISDAT that are in the XML's Header fragment.
SQL
DECLARE @f_XML_Data TABLE (
[SurveyID] [VARCHAR](15) NOT NULL,
[RecDate] [VARCHAR](15) NOT NULL,
[DisDate] [VARCHAR](15) NOT NULL,
[CMS_10] [VARCHAR](50) NULL,
[CMS_11] [VARCHAR](50) NULL,
[CMS_23] [VARCHAR](50) NULL,
[CMS_24] [VARCHAR](50) NULL,
[CMS_27] [VARCHAR](50) NULL,
[CMS_30] [VARCHAR](50) NULL,
[ITADMSRC] [VARCHAR](50) NULL,
[ITDCGSTA] [VARCHAR](50) NULL,
[ITDISDAT] [VARCHAR](50) NULL,
[ITHCAHPS] [VARCHAR](50) NULL,
[ITSERVIC] [VARCHAR](50) NULL);
INSERT @f_XML_Data VALUES
('6561942362','2024-07-10','2024-03-12','Always','Always','8','Definitely yes','Some college','English','1','6','2024-03-12','220090','Storage'),
('6561942362','2024-07-15','2024-04-10','Always','Always','9','Definitely yes','Some college','English','1','3','2024-03-12','220091','Storage');
--('6866086131','2024-07-02','2024-05-10','Always','Always','9','Definitely yes','4-yr coll. grad.','English','1','1','2024-05-10','220092','Storage'),
--('6903690620','2024-07-02','2024-05-16','Usually','Always','9','Definitely yes','4-yr coll. grad.','English','1','1','2024-05-16','220093','Removal'),
--('6978329252','2024-07-09','2024-05-21','Always','Always','10-Best possible','Definitely yes','Some college','English','1','1','2024-05-21','220094','Storage'),
--('6978332884','2024-07-09','2024-05-31','Sometimes','Usually','8','Definitely no','NULL','NULL','1','3','2024-05-31','220095','Storage'),
--('6978336149','2024-07-02','2024-05-21','Usually','Always','10-Best possible','Definitely yes','Some college','English','1','6','2024-05-21','220096','Storage'),
--('6978337433','2024-07-16','2024-06-01','Never','Always','6','Probably yes','4-yr coll. grad.','English','1','1','2024-06-01','220097','Removal'),
--('6978344956','2024-07-09','2024-06-01','Always','Usually','8','Probably yes','Some college','NULL','1','3','2024-06-01','220098','Storage'),
--('6978347260','2024-07-12','2024-05-23','Usually','Usually','10-Best possible','Definitely yes','Some college','English','1','6','2024-05-23','220099','Storage');
SELECT * FROM @f_XML_Data;
SELECT 'Forward Design' AS [company-name]
, '782837' AS [client-id]
, [SurveyID] AS [SurveyID]
, YEAR(ITDISDAT) AS [rsp-yr]
, MONTH(ITDISDAT) AS [rsp-month],
(
SELECT [ITHCAHPS] AS [administration/surveysub-id]
, YEAR(ITDISDAT) AS [administration/rsp-yr]
, MONTH(ITDISDAT) AS [administration/rsp-month]
,[ITADMSRC] AS [administration/source]
,[ITSERVIC] AS [administration/principal-reason]
,[ITDCGSTA] AS [administration/status] -- Already translated for PG in the file we send
,[CMS_30] AS [administration/language]
, DATEDIFF(DAY, CAST([DisDate] AS DATE), CAST([RecDate] AS DATE)) AS [administration/lag-time]
,'52' AS [administration/supplemental-question-count]
, [CMS_10] AS [response/cleanliness]
,[CMS_11] AS [response/quiet]
,[CMS_23] AS [response/overall-rate]
,[CMS_24] AS [response/recommend]
,[CMS_27] AS [response/education]
,[CMS_30] AS [response/language-speak]
FROM @f_XML_Data AS child
WHERE parent.SurveyID = child.SurveyID
FOR XML PATH('coredata'), TYPE
)
FROM @f_XML_Data AS parent
GROUP BY parent.SurveyID, parent.ITDISDAT
FOR XML PATH('Header'), TYPE, ROOT('monthlydata');
Output
<monthlydata>
<Header>
<company-name>Forward Design</company-name>
<client-id>782837</client-id>
<SurveyID>6561942362</SurveyID>
<rsp-yr>2024</rsp-yr>
<rsp-month>3</rsp-month>
<coredata>
<administration>
<surveysub-id>220090</surveysub-id>
<rsp-yr>2024</rsp-yr>
<rsp-month>3</rsp-month>
<source>1</source>
<principal-reason>Storage</principal-reason>
<status>6</status>
<language>English</language>
<lag-time>120</lag-time>
<supplemental-question-count>52</supplemental-question-count>
</administration>
<response>
<cleanliness>Always</cleanliness>
<quiet>Always</quiet>
<overall-rate>8</overall-rate>
<recommend>Definitely yes</recommend>
<education>Some college</education>
<language-speak>English</language-speak>
</response>
</coredata>
<coredata>
<administration>
<surveysub-id>220091</surveysub-id>
<rsp-yr>2024</rsp-yr>
<rsp-month>3</rsp-month>
<source>1</source>
<principal-reason>Storage</principal-reason>
<status>3</status>
<language>English</language>
<lag-time>96</lag-time>
<supplemental-question-count>52</supplemental-question-count>
</administration>
<response>
<cleanliness>Always</cleanliness>
<quiet>Always</quiet>
<overall-rate>9</overall-rate>
<recommend>Definitely yes</recommend>
<education>Some college</education>
<language-speak>English</language-speak>
</response>
</coredata>
</Header>
</monthlydata>