T-SQL to produce XML

Question

我们有一个列结构为：的表

• 主电话类型代码
• 主要电话号码
• 备用数字类型代码
• 备用电话号码

并要求输出以下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元素中的备用号码。

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>