Column names with the path specified as data()
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
If the path specified as column name is data()
, the value is treated as an atomic value in the generated XML. A space character is added to the XML if the next item in the serialization is also an atomic value. This is useful when you're creating list typed element and attribute values. The following query retrieves the product model ID, name, and list of products in that product model.
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"
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH('ProductModelData');
The nested SELECT retrieves a list of product IDs. It specifies "data()" as the column name for product IDs. Because PATH mode specifies an empty string for the row element name, there's no row element generated. Instead, the values are returned as assigned to a ProductIDs attribute of the <ProductModelData>
row element of the parent SELECT. This is the result:
<ProductModelData
ProductModelID = "7"
ProductModelName="HL Touring Frame"
ProductIDs="885 887 888 889 890 891 892 893"
/>
See also
Opinia
https://aka.ms/ContentUserFeedback.
Dostępne już wkrótce: W 2024 r. będziemy stopniowo wycofywać zgłoszenia z serwisu GitHub jako mechanizm przesyłania opinii na temat zawartości i zastępować go nowym systemem opinii. Aby uzyskać więcej informacji, sprawdź:Prześlij i wyświetl opinię dla