Produce XML from MS SQL, structure issues, namespace prefix:-CodePudding

I'm trying to run SQL for complex XML out of MS SQL Server 2016. I made a huge progress considering that I'm new to XML generation but still can't figure out how to do that nesting portion to make Export/Client structure matter what I tried putting into nested ROOT clause. Not sure if this issue also causing that I'm missing hmis: prefix for most Elements. I need them like on attached picture with Desired output/schema.

Also pasting self containing test Input and working code , I marked with ???? places which I think caused this trouble. Appreciate your hints. Do you think it's will be easy to do with other types FOR XML ? Explicit ??? Best Mario

SQL Version: Microsoft SQL Server 2017 (RTM-CU27)

Updated: added @export table

    /*                     --- test data/table
    DROP TABLE IF EXISTS  #t;
    SELECT * INTO #T FROM (    --    SELECT * FROM #T
    SELECT 111 PersonalID,  'Alpha' first_name, 'Brown' last_name,  '1/1/2000' birth_date, 'Manager Alpha' CaseManager_PH, 0 Female, '3/2/2022' ExportDate,  'AW3' user_updated   UNION
    SELECT 222 PersonalID,  'Bobby' , 'Dow' ,  '2/2/2002', 'Manager2222'  , 0 , '3/3/2022' ExportDate, 'BBX3'  ) A
    SELECT * FROM #T
  
  */
DECLARE @export TABLE (
       ExportDate date , StartDate DATE, EndDate date)
INSERT INTO @export (ExportDate, StartDate, EndDate)
VALUES ('3/22/2022', '1/1/2022', '4/4/2022')
    
    
        
    
;    WITH XMLNAMESPACES ('https://www.hudhdx.info/Resources/Vendors/4_0/HUD_HMIS.xsd' as hmis, 
                'http://www.w3.org/2001/XMLSchema-instance'  AS xsi )
     
    SELECT 
      10         AS [hmis:SourceID],
      111        AS [hmis:Export/ExportID]
 ,CAST(e.ExportDate  AS VARCHAR(10))   AS [hmis:Export/ExportDate]  ---<<< Change
 ,CAST(e.StartDate   AS VARCHAR(10))   AS [hmis:Export/ExportPeriod/StartDate]
 ,CAST(e.EndDate     AS VARCHAR(10))   AS [hmis:Export/ExportPeriod/EndDate]
         , (  
             SELECT 
                  ExportDate AS [hmis:Client/@DateCreated],  ExportDate AS [hmis:Client/@dateUpdated],
                  PersonalID AS [hmis:Client/PersonalID],
                  first_name AS [hmis:Client/first_name], 
                  last_name  AS [hmis:Client/last_name],
                  birth_date AS [hmis:Client/birth_date],
                    CaseManager_PH    AS [hmis:Client/CustomClientElements/CaseManager_PH],
                    'Unknown'         AS [hmis:Client/CustomClientElements/Casemanager_ContactInfo],
                  user_updated   AS [hmis:Client/user_updated]
             FROM #t  t
            -- WHERE 1=1
             FOR XML  path ,  ROOT('Export'), TYPE)  ---????
    FROM @export  e
     FOR XML  PATH('Source'), ROOT('Sources')

Desired output format:

 <hmis:Sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:hmis="https://www.hudhdx.info/Resources/Vendors/4_0/HUD_HMIS.xsd">
  <hmis:Source>
    <hmis:SourceID>10</hmis:SourceID>
    <hmis:Export>
      <ExportID>111</ExportID>
      <ExportDate>2022-03-22</ExportDate>
      <ExportPeriod>
        <StartDate>2022-01-01</StartDate>
        <EndDate>2022-04-04</EndDate>
      </ExportPeriod>
 
 <hmis:Client DateCreated="3/2/2022" DateUpdated="3/2/2022">
        <hmis:PersonalID>111</hmis:PersonalID>
        <hmis:first_name>Alpha</hmis:first_name>
        <hmis:last_name>Brown</hmis:last_name>
        <hmis:birth_date>1/1/2000</hmis:birth_date>
        <hmis:CustomClientElements>
          <hmis:CaseManager_PH>Manager Alpha</hmis:CaseManager_PH>
          <hmis:Casemanager_ContactInfo>Unknown</hmis:Casemanager_ContactInfo>
        </hmis:CustomClientElements>
        <hmis:user_updated>AW3</hmis:user_updated>
      </hmis:Client>

      <hmis:Client DateCreated="3/3/2022" DateUpdated="3/3/2022">
        <hmis:PersonalID>222</hmis:PersonalID>
        <hmis:first_name>Bobby</hmis:first_name>
        <hmis:last_name>Dow</hmis:last_name>
        <hmis:birth_date>2/2/2002</hmis:birth_date>
        <hmis:CustomClientElements>
          <hmis:CaseManager_PH>Manager2222</hmis:CaseManager_PH>
          <hmis:Casemanager_ContactInfo>Unknown</hmis:Casemanager_ContactInfo>
        </hmis:CustomClientElements>
        <hmis:user_updated>BBX3</hmis:user_updated>
      </hmis:Client>
    </hmis:Export>
  </hmis:Source>
</hmis:Sources>

CodePudding user response：

Please try the following solution.

The desired output is produced in two steps:

Raw XML via FOR XML PATH('r'), TYPE, ROOT('root').
Fine tuned final XML via XQuery .query() method and FLWOR expression.

Because a minimal reproducible example is not provided, I hope I didn't miss anything.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    PersonalID INT PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    birth_date DATE,
    CaseManager_PH VARCHAR(30),
    Female BIT,
    ExportDate DATE,
    user_updated VARCHAR(30)
    );
INSERT INTO @tbl (
    PersonalID,
    first_name,
    last_name,
    birth_date,
    CaseManager_PH,
    Female,
    ExportDate,
    user_updated
)
VALUES
(111, 'Alpha', 'Brown', '2000-01-01', 'Manager Alpha', 0, '2022-03-02', 'AW3'),
(222, 'Bobby', 'Dow', '2002-02-02', 'Manager2222', 0 , '2022-03-03', 'BBX3');

DECLARE @export TABLE (ExportDate date , StartDate DATE, EndDate date);
INSERT INTO @export (ExportDate, StartDate, EndDate) VALUES 
('2022-03-22', '2022-01-01', '2022-04-04');
-- DDL and sample data population, end

DECLARE @ExportDate date, @StartDate DATE, @EndDate DATE;
SELECT @ExportDate = ExportDate, @StartDate = StartDate, @EndDate = EndDate 
FROM @export;

WITH XMLNAMESPACES ('https://www.hudhdx.info/Resources/Vendors/4_0/HUD_HMIS.xsd' as hmis, 
            'http://www.w3.org/2001/XMLSchema-instance'  AS xsi )
SELECT (
SELECT * FROM @tbl
FOR XML PATH('r'), TYPE, ROOT('root'))
.query('<hmis:Sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <hmis:Source>
        <hmis:SourceID>10</hmis:SourceID>
        <hmis:Export>
          <ExportID>111</ExportID>
          <ExportDate>{sql:variable("@ExportDate")}</ExportDate>
          <ExportPeriod>
            <StartDate>{sql:variable("@StartDate")}</StartDate>
            <EndDate>{sql:variable("@EndDate")}</EndDate>
          </ExportPeriod>
          {
          for $x in /root/r
          return <hmis:Client DateCreated="{$x/ExportDate}" DateUpdated="{$x/ExportDate}">
                <hmis:PersonalID>{data($x/PersonalID)}</hmis:PersonalID>
                <hmis:first_name>{data($x/first_name)}</hmis:first_name>
                <hmis:last_name>{data($x/last_name)}</hmis:last_name>
                <hmis:birth_date>{data($x/birth_date)}</hmis:birth_date>
                <hmis:CustomClientElements>
                    <hmis:CaseManager_PH>{data($x/CaseManager_PH)}</hmis:CaseManager_PH>
                    <hmis:Casemanager_ContactInfo>Unknown</hmis:Casemanager_ContactInfo>
                </hmis:CustomClientElements>
                <hmis:user_updated>{data($x/user_updated)}</hmis:user_updated>
          </hmis:Client>
          }
</hmis:Export>
</hmis:Source></hmis:Sources>');

Output

<hmis:Sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:hmis="https://www.hudhdx.info/Resources/Vendors/4_0/HUD_HMIS.xsd">
  <hmis:Source>
    <hmis:SourceID>10</hmis:SourceID>
    <hmis:Export>
      <ExportID>111</ExportID>
      <ExportDate>0</ExportDate>
      <ExportPeriod>
        <StartDate>0</StartDate>
        <EndDate>0</EndDate>
      </ExportPeriod>
      <hmis:Client DateCreated="2022-03-02" DateUpdated="2022-03-02">
        <hmis:PersonalID>111</hmis:PersonalID>
        <hmis:first_name>Alpha</hmis:first_name>
        <hmis:last_name>Brown</hmis:last_name>
        <hmis:birth_date>2000-01-01</hmis:birth_date>
        <hmis:CustomClientElements>
          <hmis:CaseManager_PH>Manager Alpha</hmis:CaseManager_PH>
          <hmis:Casemanager_ContactInfo>Unknown</hmis:Casemanager_ContactInfo>
        </hmis:CustomClientElements>
        <hmis:user_updated>AW3</hmis:user_updated>
      </hmis:Client>
      <hmis:Client DateCreated="2022-03-03" DateUpdated="2022-03-03">
        <hmis:PersonalID>222</hmis:PersonalID>
        <hmis:first_name>Bobby</hmis:first_name>
        <hmis:last_name>Dow</hmis:last_name>
        <hmis:birth_date>2000-01-01</hmis:birth_date>
        <hmis:CustomClientElements>
          <hmis:CaseManager_PH>Manager2222</hmis:CaseManager_PH>
          <hmis:Casemanager_ContactInfo>Unknown</hmis:Casemanager_ContactInfo>
        </hmis:CustomClientElements>
        <hmis:user_updated>BBX3</hmis:user_updated>
      </hmis:Client>
    </hmis:Export>
  </hmis:Source>
</hmis:Sources>