Skip to content

Latest commit

 

History

History
123 lines (105 loc) · 4.96 KB

example-requesting-schemas-as-results-with-the-xmldata-and-xmlschema-options.md

File metadata and controls

123 lines (105 loc) · 4.96 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Request schemas as results with XMLDATA & XMLSCHEMA
Learn how to use the XMLDATA and XMLSCHEMA options in RAW mode with the FOR XML clause to request an XML-DATA schema or an XSD schema in the query result.
MikeRayMSFT
mikeray
randolphwest
05/05/2022
sql
xml
how-to
RAW mode, requesting schema example
RAW mode, with XMLDATA and XMLSCHEMA

Request schemas as results with XMLDATA and XMLSCHEMA

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

The following query returns the XML-DATA schema that describes the document structure.

Example

USE AdventureWorks2022;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (122, 119)
FOR XML RAW, XMLDATA;
GO

This is the result:

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="ProductModelID" dt:type="i4" />
    <AttributeType name="Name" dt:type="string" />
    <attribute type="ProductModelID" />
    <attribute type="Name" />
  </ElementType>
</Schema>
<row xmlns="x-schema:#Schema1" ProductModelID="122" Name="All-Purpose Bike Stand" />
<row xmlns="x-schema:#Schema1" ProductModelID="119" Name="Bike Wash" />

Note

The <Schema> is declared as a namespace. To avoid namespace collisions when multiple XML-Data schemas are requested in different FOR XML queries, the namespace identifier, Schema1 in this example, changes with every query execution. The namespace identifier is made up of Scheman where n is an integer.

By specifying the XMLSCHEMA option, you can request the XSD schema for the result.

USE AdventureWorks2022;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (122, 119)
FOR XML RAW, XMLSCHEMA;
GO

This is the 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" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:attribute name="ProductModelID" type="sqltypes:int" use="required" />
      <xsd:attribute name="Name" use="required">
        <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[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>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" ProductModelID="122" Name="All-Purpose Bike Stand" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" ProductModelID="119" Name="Bike Wash" />

You can specify the target namespace URI as an optional argument to XMLSCHEMA in FOR XML. This returns the specified target namespace in the schema. This target namespace remains the same every time you execute the query. For example, the following modified version of the previous query includes the namespace URI, 'urn:example.com', as an argument.

USE AdventureWorks2022;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (122, 119)
FOR XML RAW, XMLSCHEMA ('urn:example.com');
GO

This is the result:

<xsd:schema targetNamespace="urn:example.com" 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:attribute name="ProductModelID" type="sqltypes:int" use="required" />
      <xsd:attribute name="Name" use="required">
        <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[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>
<row xmlns="urn:example.com" ProductModelID="122" Name="All-Purpose Bike Stand" />
<row xmlns="urn:example.com" ProductModelID="119" Name="Bike Wash" />

See also