Generate an inline XSD schema

บทความ
02/28/2023

Applies to: SQL Server Azure SQL Database

In a FOR XML clause, you can request that your query return an inline schema together with the query results. If you want an XDR schema, you use the XMLDATA keyword in the FOR XML clause. If you want an XSD schema, you use the XMLSCHEMA keyword.

This article describes the XMLSCHEMA keyword and explains the structure of the resulting inline XSD schema. Following are the limitations when you're requesting inline schemas:

You can specify XMLSCHEMA only in RAW and AUTO mode, not in EXPLICIT mode.
If a nested FOR XML query specifies the TYPE directive, the query result is of xml type, and this result is treated as an instance of untyped XML data. For more information, see XML Data (SQL Server).

When you specify XMLSCHEMA in a FOR XML query, you receive both a schema and XML data, the query result. Each top-level element of the data refers to the previous schema by using a default namespace declaration that, in turn, refers to the target namespace of the inline schema.

For example:

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" 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.ProductModel">
    <xsd:complexType>
      <xsd:attribute name="ProductModelID" type="sqltypes:int" use="required" />
      <xsd:attribute name="Name" use="required">
        <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks2022].[dbo].[Name]">
          <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
            <xsd:maxLength value="50" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<Production.ProductModel xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" ProductModelID="1" Name="Classic Vest" />

The result includes XML schema and the XML result. The <ProductModel> top-level element in the result refers to the schema by using the default namespace declaration, xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" .

The schema part of the result may contain multiple schema documents that describe multiple namespaces. At a minimum, the following two schema documents are returned:

One schema document for the Sqltypes namespace, and for which the base SQL types are being returned.
Another schema document that describes the shape of the FOR XML query result.

Also, if any typed xml data types are included in the query result, the schemas associated with those typed xml data types are included.

The target namespace of the schema document that describes the shape of the FOR XML result contains a fixed portion and a numeric portion that increments automatically. The format of this namespace is shown in the following where n is a positive integer. For example, in the previous query, urn:schemas-microsoft-com:sql:SqlRowSet1 is the target namespace.

urn:schemas-microsoft-com:sql:SqlRowSetn

The change in the target namespaces in the result that occurred from one execution to another may not be desirable. For example, if you query the resulting XML, the change in target namespace will require that you update your query. You can optionally specify a target namespace when the XMLSCHEMA option is added in the FOR XML clause. The resulting XML will include the namespace you provided and will remain the same, regardless of how many times you run the query.

SELECT ProductModelID, Name
FROM   Production.ProductModel
WHERE ProductModelID=1
FOR XML AUTO, XMLSCHEMA ('MyURI');

Entity elements

In order to discuss the details of the XSD schema structure generated for the query result, the entity element has to first be described

An entity element in the XML data returned by FOR XML query is an element that is generated from a table and not from a column. For example, the following FOR XML query returns contact information from the Person table in the AdventureWorks2022 database.

SELECT BusinessEntityID, FirstName
FROM Person.Person
WHERE BusinessEntityID = 1
FOR XML AUTO, ELEMENTS;

This is the result:

<Person>
  <BusinessEntityID>1</BusinessEntityID>
  <FirstName>Ken</FirstName>
</Person>

In this result, <Person> is the entity element. There can be multiple entity elements in the XML result and each of these has a global declaration in the inline XSD schema. For example, the following query retrieves sales order header and detail information for a specific order.

SELECT  SalesOrderHeader.SalesOrderID, ProductID, OrderQty
FROM    Sales.SalesOrderHeader, Sales.SalesOrderDetail
WHERE   SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
AND     SalesOrderHeader.SalesOrderID=5001
FOR XML AUTO, ELEMENTS, XMLSCHEMA;

Because the query specifies the ELEMENTS directive, the resulting XML is element-centric. The query also specifies the XMLSCHEMA directive. Therefore, an inline XSD schema is returned. This is the result:

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" 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="Sales.SalesOrderHeader">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="SalesOrderID" type="sqltypes:int" />
        <xsd:element ref="schema:Sales.SalesOrderDetail" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="Sales.SalesOrderDetail">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ProductID" type="sqltypes:int" />
        <xsd:element name="OrderQty" type="sqltypes:smallint" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Note the following from the previous query:

In the result, <SalesOrderHeader> and <SalesOrderDetail> are entity elements. Because of this, they're globally declared in the schema. That is, the declaration appears at the top level inside the <Schema> element.
The <SalesOrderID>, <ProductID>, and <OrderQty> aren't entity elements, because they map to columns. The column data is returned as elements in the XML, because of the ELEMENTS directive. These are mapped to local elements of the entity element's complex type. If the ELEMENTS directive isn't specified, the SalesOrderID, ProductID and OrderQty values are mapped to local attributes of the corresponding entity element's complex type.

Attribute name clashes

The following discussion is based on the CustOrder and CustOrderDetail tables. To test the following samples, create these tables and add your own sample data:

CREATE TABLE CustOrder (OrderID int primary key, CustomerID int);
GO
CREATE TABLE CustOrderDetail (OrderID int, ProductID int, Qty int);
GO

In FOR XML, the same name is sometimes used to indicate different properties, attributes. For example, the following attribute-centric RAW mode query generates two attributes that have the same name, OrderID. This generates an error.

SELECT CustOrder.OrderID,
       CustOrderDetail.ProductID,
       CustOrderDetail.OrderID
FROM   dbo.CustOrder, dbo.CustOrderDetail
WHERE  CustOrder.OrderID = CustOrderDetail.OrderID
FOR XML RAW, XMLSCHEMA;

However, because it's acceptable to have two elements that have the same name, you can eliminate the problem by adding the ELEMENTS directive:

SELECT CustOrder.OrderID,
       CustOrderDetail.ProductID,
       CustOrderDetail.OrderID
from   dbo.CustOrder, dbo.CustOrderDetail
where  CustOrder.OrderID = CustOrderDetail.OrderID
FOR XML RAW, XMLSCHEMA, ELEMENTS;

This is the result. Note in the inline XSD schema, the OrderID element is defined two times. One of the declarations has minOccurs set to 0, corresponding to the OrderID from the CustOrderDetail table, and the second one maps to the OrderID primary key column of the CustOrder table where minOccurs is 1 by default.

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" 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="row">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="OrderID" type="sqltypes:int" />
        <xsd:element name="ProductID" type="sqltypes:int" minOccurs="0" />
        <xsd:element name="OrderID" type="sqltypes:int" minOccurs="0" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Element name clashes

In FOR XML, the same name can be used to indicate two subelements. For example, the following query retrieves ListPrice and DealerPrice values of products, but the query specifies the same alias, Price, for these two columns. Therefore, the resulting rowset will have two columns with same name.

Case 1: Both subelements are nonkey columns of the same type and can be NULL

In the following query, both subelements are nonkey columns of the same type and can be NULL.

DROP TABLE T;
GO
CREATE TABLE T (ProductID int primary key, ListPrice money, DealerPrice money);
GO
INSERT INTO T values (1, 1.25, null);
GO

SELECT ProductID, ListPrice Price, DealerPrice Price
FROM   T
for    XML RAW, ELEMENTS, XMLSCHEMA;

This is the corresponding XML generated. Only a fraction of the inline XSD is shown:

...
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" />
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ProductID" type="sqltypes:int" />
        <xsd:element name="Price" type="sqltypes:money" minOccurs="0" maxOccurs="2" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
  <ProductID>1</ProductID>
  <Price>1.2500</Price>
</row>

Note the following in the inline XSD schema:

Both the ListPrice and DealerPrice are of the same type, money, and both can be NULL in the table. Therefore, because they may not be returned in the resulting XML, there is only one <Price> child element in the complex type declaration of the <row> element that has minOccurs=0 and maxOccurs=2.
In the result, because the DealerPrice value is NULL in the table, only ListPrice is returned as a <Price> element. If you add the XSINIL parameter to the ELEMENTS directive, you will receive both of the elements that have the xsi:nil value set to TRUE for the<Price> element that corresponds to DealerPrice. You will also receive two <Price> child elements in the <row> complex type definition in the inline XSD schema with the nillable attribute set to TRUE for both. This fragment is a partial result:

...
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" />
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ProductID" type="sqltypes:int" nillable="1" />
        <xsd:element name="Price" type="sqltypes:money" nillable="1" />
        <xsd:element name="Price" type="sqltypes:money" nillable="1" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>1</ProductID>
  <Price>1.2500</Price>
  <Price xsi:nil="true" />
</row>

Case 2: One key and one nonkey column of the same type

The following query illustrates one key and one nonkey column of the same type.

CREATE TABLE T (Col1 int primary key, Col2 int, Col3 nvarchar(20));
GO
INSERT INTO T VALUES (1, 1, 'test');
GO

The following query against table T specifies the same alias for Col1 and Col2, where Col1 is a primary key and cannot be null, and Col2 can be null. This generates two sibling elements that are children of the <row> element.

SELECT Col1 as Col, Col2 as Col, Col3
FROM T
FOR XML RAW, ELEMENTS, XMLSCHEMA

This is the result. Only a fragment of the inline XSD schema is shown.

...
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" />
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Col" type="sqltypes:int" />
        <xsd:element name="Col" type="sqltypes:int" minOccurs="0" />
        <xsd:element name="Col3" minOccurs="0">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="20" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
  <Col>1</Col>
  <Col>1</Col>
  <Col3>test</Col3>
</row>

Note in the inline XSD schema that the <Col> element corresponding to the Col2 has minOccurs set to 0.

Case 3: Both elements of different types and corresponding columns can be NULL

The following query is specified against the sample table shown in case 2:

SELECT Col1, Col2 as Col, Col3 as Col
FROM T
FOR XML RAW, ELEMENTS, XMLSCHEMA;

In the following query, Col2 and Col3 are given the same aliases. This generates two sibling elements that have the same name and that are both children of the <raw> element in the result. Both of these columns are of different types and both can be NULL. This is the result. Only partial inline XSD schema is shown.

...
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" />
  <xsd:simpleType name="Col1">
    <xsd:restriction base="sqltypes:int" />
  </xsd:simpleType>
  <xsd:simpleType name="Col2">
    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
      <xsd:maxLength value="20" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Col1" type="sqltypes:int" />
        <xsd:element name="Col" minOccurs="0" maxOccurs="2" type="xsd:anySimpleType" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
  <Col1>1</Col1>
  <Col xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Col1">1</Col>
  <Col xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Col2">test</Col>
</row>