sql-server - T-SQL 生成 XML

Question

我们有一个表，其列结构为：

• MainPhoneTypeCode
• 主电话号码
• AlternateNumberTypeCode
• 备用电话号码

并要求输出以下 XML：

  <ns0:PhoneNumberList>
    <ns0:PhoneNumber Type="0096">
      <ns0:Number>[phone]</ns0:Number>
    </ns0:PhoneNumber>
    <ns0:PhoneNumber Type="0026">
      <ns0:Number>[phone]</ns0:Number>
    </ns0:PhoneNumber>
  </ns0:PhoneNumberList>

到目前为止，对于主要电话，我有：

WITH xmlnamespaces ('http://www.sifassociation.org/datamodel/au/3.4' AS ns0)
SELECT  si.MainPhoneTypeCode AS [@Type]
       ,si.MainPhoneNumber   AS [ns0:Number]
FROM    EDU.tbl_EDU_SchoolInfo si
WHERE   si.SchoolInfoID = 4
FOR XML PATH('ns0:PhoneNumber'), TYPE, ROOT('ns0:PhoneNumberList')

生成：

<ns0:PhoneNumberList xmlns:ns0="http://www.sifassociation.org/datamodel/au/3.4">
  <ns0:PhoneNumber Type="0096">
    <ns0:Number>[phone]</ns0:Number>
  </ns0:PhoneNumber>
</ns0:PhoneNumberList>

但不确定如何添加要包含在 PhoneNumberList 元素中的备用号码。

标签： sql-serverxmltsqlxpath

Answer 1

需要命名空间声明，因为所有 XML 标记中都有命名空间前缀。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE 
(
    SchoolInfoID  INT PRIMARY KEY
    , MainPhoneTypeCode CHAR(4)
    , MainPhoneNumber VARCHAR(20)
    , AlternateNumberTypeCode CHAR(4)
    , AlternatePhoneNumber VARCHAR(20)
);
INSERT INTO @tbl (
    SchoolInfoID,
    MainPhoneTypeCode,
    MainPhoneNumber,
    AlternateNumberTypeCode,
    AlternatePhoneNumber
)
VALUES
(4, '0096', '[phone]', '0026', '[phone]');
-- DDL and sample data population, end

;WITH XMLNAMESPACES ('http://www.sifassociation.org/datamodel/au/3.4' AS ns0)
SELECT  MainPhoneTypeCode AS [ns0:PhoneNumber/@Type]
       , MainPhoneNumber   AS [ns0:PhoneNumber/ns0:Number]
       , ''
       , AlternateNumberTypeCode AS [ns0:PhoneNumber/@Type]
       , AlternatePhoneNumber   AS [ns0:PhoneNumber/ns0:Number]
FROM @tbl
WHERE SchoolInfoID = 4
FOR XML PATH(''), TYPE, ROOT('ns0:PhoneNumberList');

输出

<ns0:PhoneNumberList xmlns:ns0="http://www.sifassociation.org/datamodel/au/3.4">
  <ns0:PhoneNumber Type="0096">
    <ns0:Number>[phone]</ns0:Number>
  </ns0:PhoneNumber>
  <ns0:PhoneNumber Type="0026">
    <ns0:Number>[phone]</ns0:Number>
  </ns0:PhoneNumber>
</ns0:PhoneNumberList>