示例:使用 PATH 模式
适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例
下面的示例演示如何使用 PATH 模式通过 SELECT 查询生成 XML。 这些查询中有许多都是针对 ProductModel 表的 Instructions 列中存储的自行车生产说明 XML 文档指定的。
指定 PATH 模式查询
下面的查询指定了一个 FOR XML PATH 模式。
USE AdventureWorks2022;
GO
SELECT
ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH;
GO
以下结果是以元素为中心的 XML,在该 XML 中,生成的行集中的每个列值都包在元素中。 由于 SELECT
子句未指定任何列名别名,因此生成的子元素名称与 SELECT
子句中相应的列名相同。 针对行集中的每一行,将添加一个 <row>
标记。
<row>
<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
</row>
<row>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
</row>
以下结果与指定 RAW
选项的 ELEMENTS
模式查询的结果相同。 它将返回以元素为中心的 XML,在该 XML 中,结果集中的每一行都有一个相应的默认 <row>
元素。
USE AdventureWorks2022;
GO
SELECT ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW, ELEMENTS;
可以选择指定行元素名称,以覆盖默认的 <row>
>。 例如,以下查询将针对行集中的每一行返回相应的 <ProductModel>
元素。
USE AdventureWorks2022;
GO
SELECT ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModel');
GO
生成的 XML 将包含指定的行元素名称。
<ProductModel>
<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
</ProductModel>
如果指定零长度字符串,则将不生成包装元素。
USE AdventureWorks2022;
GO
SELECT ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('');
GO
结果如下:
<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
指定类似 XPath 的列名
在下面的查询中,指定的 ProductModelID
列名以“@”开头,且不包含左斜线(“/”)。 因此,在生成的 XML 中,将创建包含相应列值的 <row>
元素的属性。
USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@id",
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('ProductModelData');
GO
结果如下:
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
可以通过在 root
中指定 FOR XML
选项来添加单个顶级元素。
SELECT ProductModelID AS "@id",
Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModelData'), root ('Root');
GO
若要生成层次结构,可以包含类似 PATH 的语法。 例如,将 Name
列的列名改为“SomeChild/ModelName”,就会获得具有层次结构的 XML,如以下结果所示:
<Root>
<ProductModelData id="122">
<SomeChild>
<ModelName>All-Purpose Bike Stand</ModelName>
</SomeChild>
</ProductModelData>
<ProductModelData id="119">
<SomeChild>
<ModelName>Bike Wash</ModelName>
</SomeChild>
</ProductModelData>
</Root>
除了产品型号 ID 和名称以外,以下查询还将检索产品型号的生产说明位置。 由于 Instructions 列是 xml 类型,因此指定了 xml 数据类型的 query()
方法来检索该位置。
SELECT ProductModelID AS "@id",
Name,
Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location
') AS ManuInstr
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH ('ProductModelData'), root ('Root');
GO
下面是部分结果: 由于此查询指定 ManuInstr 作为列名,因此 query()
方法返回的 XML 包装在<ManuInstr>
标记中,如下所示:
<Root>
<ProductModelData id="7">
<Name>HL Touring Frame</Name>
<ManuInstr>
<MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
<MI:step>...</MI:step>...
</MI:Location>
...
</ManuInstr>
</ProductModelData>
</Root>
在上一个 FOR XML 查询中,建议包含针对 <Root>
和 <ProductModelData>
元素的命名空间。 首先使用 WITH XMLNAMESPACES 定义命名空间绑定的前缀,然后在 FOR XML 查询中使用前缀,即可达到此目的。 有关详细信息,请参阅 使用 WITH XMLNAMESPACES 将命名空间添加到查询。
USE AdventureWorks2022;
GO
WITH XMLNAMESPACES (
'uri1' AS ns1,
'uri2' AS ns2,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)
SELECT ProductModelID AS "ns1:ProductModelID",
Name AS "ns1:Name",
Instructions.query('
/MI:root/MI:Location
')
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH ('ns2:ProductInfo'), root('ns1:root');
GO
在 MI
中也定义了 WITH XMLNAMESPACES
前缀。 因此,所指定 xml 类型的 query()
方法没有在查询 prolog 中定义此前缀。 结果如下:
<ns1:root xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">
<ns2:ProductInfo>
<ns1:ProductModelID>7</ns1:ProductModelID>
<ns1:Name>HL Touring Frame</ns1:Name>
<MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10" xmlns="">
<MI:step>
Insert <MI:material>aluminum sheet MS-2341</MI:material> into the <MI:tool>T-85A framing tool</MI:tool>.
</MI:step>
...
</MI:Location>
...
</ns2:ProductInfo>
</ns1:root>
使用 PATH 模式生成值列表
对于每个产品型号,此查询将构造一个由产品 ID 组成的值列表。 对于每个产品 ID,此查询还会构造 <ProductName>
嵌套元素,如下面的 XML 段落所示:
<ProductModelData ProductModelID="7" ProductModelName="..." ProductIDs="product id list in the product model">
<ProductName>...</ProductName>
<ProductName>...</ProductName>
...
</ProductModelData>
以下是用于生成所需 XML 的查询:
USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@ProductModelID",
Name AS "@ProductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')) AS "@ProductIDs",
(SELECT Name AS "ProductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')) AS "ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 or ProductModelID=9
FOR XML PATH('ProductModelData');
请注意上述查询的以下方面:
通过使用
SELECT
作为列名,第一个嵌套data()
语句将返回 ProductID 的列表。 由于此查询指定了一个空字符串作为FOR XML PATH
中的行元素名,因此不会生成元素。 相反,值列表将被分配给ProductID
属性。第二个嵌套
SELECT
语句检索该产品型号中的产品的产品名。 它将生成<ProductName>
元素,并将这些元素包装在<ProductNames>
元素中返回,因为该查询指定ProductNames
作为列名。
下面是部分结果:
<ProductModelData PId="7" ProductModelName="HL Touring Frame" ProductIDs="885 887 ...">
<ProductNames>
<ProductName>HL Touring Frame - Yellow, 60</ProductName>
<ProductName>HL Touring Frame - Yellow, 46</ProductName>
</ProductNames>
...
</ProductModelData>
<ProductModelData PId="9" ProductModelName="LL Road Frame" ProductIDs="722 723 724 ...">
<ProductNames>
<ProductName>LL Road Frame - Black, 58</ProductName>
<ProductName>LL Road Frame - Black, 60</ProductName>
<ProductName>LL Road Frame - Black, 62</ProductName>
...
</ProductNames>
</ProductModelData>
构造产品名的子查询将返回已实体化并随后添加到 XML 的字符串。 如果添加 type 指令 FOR XML PATH (''), type
,则该子查询将返回 xml 类型的结果,并且不会进行实体化。
USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@ProductModelID",
Name AS "@ProductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')
) AS "@ProductIDs",
(
SELECT Name AS "ProductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH (''), type
) AS "ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData');
在生成的 XML 中添加命名空间
如 使用 WITH XMLNAMESPACES 添加命名空间中所述,您可以使用 WITH XMLNAMESPACES 在 PATH 模式查询中包含命名空间。 例如,SELECT 子句中所指定的名称包含命名空间前缀。 以下 PATH
模式查询将构造带命名空间的 XML。
SELECT 'en' as "English/@xml:lang",
'food' as "English",
'ger' as "German/@xml:lang",
'Essen' as "German"
FOR XML PATH ('Translation')
GO
添加到 <English>
元素的 @xml:lang
属性在预定义的 XML 命名空间中定义。
结果如下:
<Translation>
<English xml:lang="en">food</English>
<German xml:lang="ger">Essen</German>
</Translation>
以下查询与示例 C 类似,只不过它使用 WITH XMLNAMESPACES
在 XML 结果中包含命名空间。 有关详细信息,请参阅 使用 WITH XMLNAMESPACES 将命名空间添加到查询。
USE AdventureWorks2022;
GO
WITH XMLNAMESPACES ('uri1' AS ns1, DEFAULT 'uri2')
SELECT ProductModelID AS "@ns1:ProductModelID",
Name AS "@ns1:ProductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')
) AS "@ns1:ProductIDs",
(
SELECT ProductID AS "@ns1:ProductID",
Name AS "@ns1:ProductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH , type
) AS "ns1:ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');
结果如下:
<root xmlns="uri2"
xmlns:ns1="uri1">
<ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">
<ns1:ProductNames>
<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />
<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />
...
</ns1:ProductNames>
</ProductModelData>
<ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
<ns1:ProductNames>
<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />
...
</ns1:ProductNames>
</ProductModelData>
</root>
另请参阅
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈