從MSSQL、結構問題、命名空間前綴生成XML：-有解無憂

我正在嘗試從 MS SQL Server 2016 中為復雜的 XML 運行 SQL。考慮到我是 XML 生成的新手，但仍然無法弄清楚如何進行嵌套部分來制作匯出/客戶端結構，我取得了巨大的進步不管我嘗試將什么放入嵌套的 ROOT 子句中。不確定這個問題是否也導致我缺少 hmis: 大多數元素的前綴。我需要它們，就像附加圖片一樣，帶有所需的輸出/模式。

還粘貼自包含測驗輸入和作業代碼，我用 ???? 我認為造成這個麻煩的地方。感謝您的提示。您認為使用其他型別的 FOR XML 會很容易嗎？明確的？？？最佳馬里奧

SQL 版本：Microsoft SQL Server 2017 (RTM-CU27)

更新：添加了@export 表

    /*                     --- 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')

所需的輸出格式：

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

uj5u.com熱心網友回復：

請嘗試以下解決方案。

所需的輸出分兩步產生：

原始 XML 通過FOR XML PATH('r'), TYPE, ROOT('root').
.query()通過 XQuery方法和 FLWOR 運算式微調最終 XML 。

因為沒有提供最小的可重現示例，我希望我沒有錯過任何東西。

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>');

輸出

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451044.html

標籤：sql服务器 xml tsql

上一篇：如何計算沒有。sqlserver中特定時間段一年中的天數

下一篇：解決“Latin1_General_CI_AS”和“SQL_Latin1_General_CP1_CI_AS”之間的排序規則沖突