Delen via

Vorm XML met geneste FOR XML-query's

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In het volgende voorbeeld wordt een query uitgevoerd op de Production.Product tabel om de ListPrice en StandardCost waarden van een specifiek product op te halen. Om de query interessant te maken, worden beide prijzen geretourneerd in een <Price> element en elk <Price> element heeft een PriceType kenmerk.

Voorbeeld

Dit is de verwachte vorm van de XML:

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
  <Price xmlns="" PriceType="ListPrice">133.34</Price>
  <Price xmlns="" PriceType="StandardCost">98.77</Price>
</Production.Product>

Dit is de geneste FOR XML-query:

USE AdventureWorks2022;
GO
SELECT Product.ProductID,
          (SELECT 'ListPrice' as PriceType,
                   CAST(CAST(ListPrice as NVARCHAR(40)) as XML)
           FROM    Production.Product Price
           WHERE   Price.ProductID=Product.ProductID
           FOR XML AUTO, TYPE),
          (SELECT  'StandardCost' as PriceType,
                   CAST(CAST(StandardCost as NVARCHAR(40)) as XML)
           FROM    Production.Product Price
           WHERE   Price.ProductID=Product.ProductID
           FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA;

Let op het volgende uit de vorige query:

  • De buitenste SELECT-instructie bouwt het <Product>-element dat een attribuut ProductID en twee kindelementen <Price> heeft.

  • De twee interne SELECT-instructies maken twee <Price> elementen, elk met een kenmerk PriceType en XML waarmee de productprijs wordt geretourneerd.

  • De XMLSCHEMA-instructie in de buitenste SELECT-instructie genereert het inline XSD-schema dat de vorm van de resulterende XML beschrijft.

Als u de query interessant wilt maken, kunt u de FOR XML-query schrijven en vervolgens een XQuery schrijven op basis van het resultaat om de XML opnieuw vorm te geven, zoals wordt weergegeven in de volgende query:

SELECT ProductID,
( SELECT p2.ListPrice, p2.StandardCost
   FROM Production.Product p2
   WHERE Product.ProductID = p2.ProductID
   FOR XML AUTO, ELEMENTS XSINIL, type ).query('
                                   for $p in /p2/*
                                   return
                                    <Price PriceType = "{local-name($p)}">
                                     { data($p) }
                                    </Price>
                                  ')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE;

In het vorige voorbeeld wordt de query() methode van het xml- gegevenstype gebruikt om een query uit te voeren op de XML die wordt geretourneerd door de interne FOR XML-query en het verwachte resultaat samen te stellen.

Dit is het resultaat:

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>

Zie ook