sql-serverxmlt-sql

Stopping repeated XML Headers


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.


Solution

  • 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>