使用 WITH XMLNAMESPACES 將命名空間加入至查詢Add Namespaces to Queries with WITH XMLNAMESPACES

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

WITH XMLNAMESPACES (Transact-SQL) 會以下列方式支援命名空間 URI:WITH XMLNAMESPACES (Transact-SQL) provides namespace URI support in the following way:

在 FOR XML 查詢中使用 WITH XMLNAMESPACESUsing WITH XMLNAMESPACES in the FOR XML Queries

WITH XMLNAMESPACES 讓您可以在 FOR XML 查詢中包括 XML 命名空間。WITH XMLNAMESPACES lets you include XML namespaces in FOR XML queries. 例如,請看下列的 FOR XML 查詢:For example, consider the following FOR XML query:

SELECT ProductID, Name, Color  
FROM   Production.Product  
WHERE  ProductID=316 or ProductID=317  
FOR XML RAW  

以下是結果:This is the result:

<row ProductID="316" Name="Blade" />  
<row ProductID="317" Name="LL Crankarm" Color="Black" />  
  

若要將命名空間加入到 FOR XML 查詢所建構的 XML 中,請先使用 WITH NAMESPACES 子句,指定命名空間前置詞到 URI 的對應。To add namespaces to the XML constructed by the FOR XML query, first specify the namespace prefix to URI mappings by using the WITH NAMESPACES clause. 接著,使用命名空間前置詞,在查詢中指定名稱,如下列修改過的查詢所示。Then, use the namespace prefixes in specifying the names in the query as shown in the following modified query. 請注意,WITH XMLNAMESPACES 子句會指定命名空間前置詞 (ns1) 到 URI (uri) 的對應。Note that the WITH XMLNAMESPACES clause specifies the namespace prefix (ns1) to URI (uri) mapping. 然後 ns1 前置詞會用來指定 FOR XML 查詢要建構的元素及屬性名稱。The ns1 prefix is then used in specifying the element and attribute names to be constructed by the FOR XML query.

WITH XMLNAMESPACES ('uri' as ns1)  
SELECT ProductID as 'ns1:ProductID',  
       Name      as 'ns1:Name',   
       Color     as 'ns1:Color'  
FROM Production.Product  
WHERE ProductID=316 or ProductID=317  
FOR XML RAW ('ns1:Prod'), ELEMENTS  
  

XML 結果會包括命名空間前置詞:The XML result includes the namespace prefixes:

<ns1:Prod xmlns:ns1="uri">  
  <ns1:ProductID>316</ns1:ProductID>  
  <ns1:Name>Blade</ns1:Name>  
</ns1:Prod>  
<ns1:Prod xmlns:ns1="uri">  
  <ns1:ProductID>317</ns1:ProductID>  
  <ns1:Name>LL Crankarm</ns1:Name>  
  <ns1:Color>Black</ns1:Color>  
</ns1:Prod>  
  

下列適用於 WITH XMLNAMESPACES 子句:The following applies to the WITH XMLNAMESPACES clause:

  • 只有 FOR XML 查詢的 RAW、AUTO 及 PATH 模式才支援。It is supported only on the RAW, AUTO, and PATH modes of the FOR XML queries. 不支援 EXPLICIT 模式。The EXPLICIT mode is not supported.

  • 它只會影響 FOR XML 查詢的命名空間前置詞及 xml 資料類型方法,但不會影響 XML 剖析器。It only affects the namespace prefixes of FOR XML queries and the xml data type methods, but not the XML parser. 例如,下列查詢會傳回錯誤,因為 XML 文件沒有 myNS 前置詞的命名空間宣告。For example, the following query returns an error, because the XML document has no namespace declaration for the myNS prefix.

  • 如果正在使用 WITH XMLNAMESPACES 子句,就不能使用 FOR XML 指示詞、XMLSCHEMA 及 XMLDATA。The FOR XML directives, XMLSCHEMA and XMLDATA cannot be used when a WITH XMLNAMESPACES clause is being used.

    CREATE TABLE T (x xml)  
    go  
    WITH XMLNAMESPACES ('https://abc' as myNS )  
    INSERT INTO T VALUES('<myNS:root/>')  
    

使用 XSINIL 指示詞Using the XSINIL Directive

若您正在使用 ELEMENTS XSINIL 指示詞,就無法在 WITH XMLNAMESPACES 子句中定義 xsi 前置詞。You cannot define the xsi prefix in the WITH XMLNAMESPACES clause if you are using the ELEMENTS XSINIL directive. 不過,它會在您使用 ELEMENTS XSINIL 時自動加入。Instead, it is added automatically when you use ELEMENTS XSINIL. 下列查詢會使用 ELEMENTS XSINIL,以產生元素中心的 XML,其中 Null 值會對應到 xsi:nil 屬性設為 True 的元素。The following query uses ELEMENTS XSINIL that generates element-centric XML where null values are mapped to elements that have the xsi:nil attribute set to True.

WITH XMLNAMESPACES ('uri' as ns1)  
SELECT ProductID as 'ns1:ProductID',  
       Name      as 'ns1:Name',   
       Color     as 'ns1:Color'  
FROM Production.Product  
WHERE ProductID=316   
FOR XML RAW, ELEMENTS XSINIL  

以下是結果:This is the result:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns1="uri">  
  <ns1:ProductID>316</ns1:ProductID>  
  <ns1:Name>Blade</ns1:Name>  
  <ns1:Color xsi:nil="true" />  
</row>  

指定預設命名空間Specifying Default Namespaces

您可以使用 DEFAULT 關鍵字來宣告預設的命名空間,而不需要宣告命名空間前置詞。Instead of declaring a namespace prefix, you can declare a default namespace by using a DEFAULT keyword. 在 FOR XML 查詢中,它會將預設的命名空間繫結到產生之 XML 中的 XML 節點。In the FOR XML query, it will bind the default namespace to XML nodes in the resulting XML. 在下面的範例中,WITH XMLNAMESPACES 定義兩個連同預設命名空間一起定義的命名空間前置詞。In the following example, the WITH XMLNAMESPACES defines two namespace prefixes that are defined together with a default namespace.

WITH XMLNAMESPACES ('uri1' as ns1,   
                    'uri2' as ns2,  
                    DEFAULT 'uri2')  
SELECT ProductID,   
      Name,  
      Color  
FROM Production.Product   
WHERE ProductID=316 or ProductID=317  
FOR XML RAW ('ns1:Product'), ROOT('ns2:root'), ELEMENTS  

此 FOR XML 查詢會產生元素中心的 XML。The FOR XML query generates element-centric XML. 請注意,此查詢在命名節點時兩個命名空間前置詞都會使用。Note that the query uses both the namespace prefixes in naming nodes. 在 SELECT 子句中,ProductID、Name 及 Color 並未指定具有任何前置詞的名稱。In the SELECT clause, the ProductID, Name, and Color do not specify a name with any prefix. 因此,產生之 XML 中的對應元素會隸屬於預設的命名空間。Therefore, the corresponding elements in the resulting XML belong to the default namespace.

<ns2:root xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">  
  <ns1:Product>  
    <ProductID>316</ProductID>  
    <Name>Blade</Name>  
  </ns1:Product>  
  <ns1:Product>  
    <ProductID>317</ProductID>  
    <Name>LL Crankarm</Name>  
    <Color>Black</Color>  
  </ns1:Product>  
</ns2:root>  

下列查詢和前一個查詢很類似,差別只在下列查詢指定了 FOR XML AUTO 模式。The following query is similar to the previous one, except that the FOR XML AUTO mode is specified.

WITH XMLNAMESPACES ('uri1' as ns1,  'uri2' as ns2,DEFAULT 'uri2')  
SELECT ProductID,   
      Name,  
      Color  
FROM Production.Product as "ns1:Product"  
WHERE ProductID=316 or ProductID=317  
FOR XML AUTO, ROOT('ns2:root'), ELEMENTS  

使用預先定義的命名空間Using Predefined Namespaces

使用預先定義的命名空間時,除了使用 ELEMENTS XSINIL 時的 xml 命名空間及 xsi 命名空間之外,您必須使用 WITH XMLNAMESPACES 明確地指定命名空間繫結。When you use predefined namespaces, except the xml namespace and the xsi namespace when ELEMENTS XSINIL is used, you must explicitly specify the namespace binding by using WITH XMLNAMESPACES. 下列查詢針對預先定義的命名空間 (urn:schemas-microsoft-com:xml-sql) 明確地定義了命名空間前置詞到 URI 的繫結。The following query explicitly defines the namespace prefix to URI binding for the predefined namespace (urn:schemas-microsoft-com:xml-sql).

WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-sql' as sql)  
SELECT 'SELECT * FROM Customers FOR XML AUTO, ROOT("a")' AS "sql:query"  
FOR XML PATH('sql:root')  

以下是結果。This is the result. SQLXML 的使用者對這個 XML 範本很熟悉。SQLXML users are familiar with this XML template. 如需詳細資訊,請參閱 SQLXML 4.0 程式設計概念For more information, see SQLXML 4.0 Programming Concepts.

<sql:root xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
  <sql:query>SELECT * FROM Customers FOR XML AUTO, ROOT("a")</sql:query>  
</sql:root>  

只有 xml 命名空間前置詞,不用在 WITH XMLNAMESPACES 中明確定義就可以使用,如以下 PATH 模式查詢中所示。Only the xml namespace prefix can be used without explicitly defining it in WITH XMLNAMESPACES, as shown in the following PATH mode query. 同時,若前置詞已經宣告了,就必須將它繫結到命名空間 http://www.w3.org/XML/1998/namespaceAlso, if the prefix is declared, it has to be bound to the namespace http://www.w3.org/XML/1998/namespace. SELECT 子句中指定的名稱會參考不是使用 WITH XMLNAMESPACES 明確定義的 xml 命名空間前置詞。The names specified in the SELECT clause refer to the xml namespace prefix that is not explicitly defined by using WITH XMLNAMESPACES.

SELECT 'en'    as "English/@xml:lang",  
       'food'  as "English",  
       'ger'   as "German/@xml:lang",  
       'Essen' as "German"  
FOR XML PATH ('Translation')  
go  

@xml:lang 屬性會使用預先定義的 XML 命名空間。The @xml:lang attributes use the predefined xml namespace. 因為 XML 1.0 版不需要明確宣告 xml 命名空間繫結,所以結果不會包括命名空間繫結的明確宣告。Because XML version 1.0 does not require the explicit declaration of the xml namespace binding, the result will not include an explicit declaration of the namespace binding.

以下是結果:This is the result:

<Translation>  
  <English xml:lang="en">food</English>  
  <German xml:lang="ger">Essen</German>  
</Translation>  

將 WITH XMLNAMESPACES 搭配 xml 資料類型方法使用Using WITH XMLNAMESPACES with the xml Data Type Methods

在 SELECT 查詢中指定 xml 資料類型方法 (或 UPDATE 中指定 modify() 方法) 時,全部都必須在其初構中重複命名空間宣告。The xml Data Type Methods specified in a SELECT query, or in UPDATE when it is the modify() method, all have to repeat the namespace declaration in their prolog. 這可能會很費時。This can be time-consuming. 例如,下列查詢會擷取其目錄描述確實包括規格的產品型號識別碼。For example, the following query retrieves product model IDs whose catalog descriptions do include specification. 也就是說,有 <Specifications> 元素。That is, the <Specifications> element exists.

SELECT ProductModelID, CatalogDescription.query('  
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
    <Product   
        ProductModelID= "{ sql:column("ProductModelID") }"   
        />  
') AS Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist('  
    declare namespace  pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
     /pd:ProductDescription[(pd:Specifications)]'  
    ) = 1  

在上一個查詢中, query()exist() 方法都在其初構中宣告了相同的命名空間。In the previous query, both the query() and exist() methods declare the same namespace in their prolog. 例如:For example:

declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  

另外,您也可以先宣告 WITH XMLNAMESPACES,然後在查詢中使用命名空間前置詞。Alternatively, you can declare WITH XMLNAMESPACES first and use the namespace prefixes in the query. 在此情況中, query()exist() 方法就不需要在初構中包含命名空間宣告。In this case, the query() and exist() methods do not have to include namespace declarations in their prolog.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' as pd)  
SELECT ProductModelID, CatalogDescription.query('  
    <Product   
        ProductModelID= "{ sql:column("ProductModelID") }"   
        />  
') AS Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist('  
     /pd:ProductDescription[(pd:Specifications)]'  
    ) = 1  
Go  

請注意,XQuery 初構中的明確宣告會覆寫命名空間前置詞,以及 WITH 子句中定義的預設元素命名空間。Note that an explicit declaration in the XQuery prolog overrides the namespace prefix and the default element namespace that are defined in the WITH clause.

另請參閱See Also

xml 資料類型方法 xml Data Type Methods
XQuery 語言參考 (SQL Server) XQuery Language Reference (SQL Server)
WITH XMLNAMESPACES (Transact-SQL) WITH XMLNAMESPACES (Transact-SQL)
FOR XML (SQL Server)FOR XML (SQL Server)