Generating XML Schema as part of query  

Posted by ReelTym


create table complex
(
id bigint identity(1,1) primary key clustered,
sysnamecol sysname not null,
datetimecol datetime null,
bigintcol bigint null,
intcol int null,
smallintcol smallint null,
tinyintcol tinyint null,
bitcol bit default(0),
nvarcharcol nvarchar(max) null,
varcharcol varchar(max) null,
ncharcol nchar(255) null,
charcol char(255) null,
varbinarycol varbinary(max) null,
moneycol money null,
decimalcol decimal(13,9) null,
floatcol float null
)

insert into complex ( sysnamecol,datetimecol,bigintcol,intcol,smallintcol,tinyintcol,bitcol,nvarcharcol,varcharcol,ncharcol,charcol,varbinarycol,moneycol,decimalcol,floatcol)
select 'sysname column',GETDATE(),100000000,1000000,1000,100,1,'nvarchar column','varchar column','nchar column','char column',convert(varbinary(max),'varbinary column'),123.45,67.890,123456.7890123

select *
from complex
FOR XML AUTO, BINARY BASE64, ELEMENTS, XMLSCHEMA

/*
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="complex">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="id" type="sqltypes:bigint" />
<xsd:element name="sysnamecol">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="128" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="datetimecol" type="sqltypes:datetime" minOccurs="0" />
<xsd:element name="bigintcol" type="sqltypes:bigint" minOccurs="0" />
<xsd:element name="intcol" type="sqltypes:int" minOccurs="0" />
<xsd:element name="smallintcol" type="sqltypes:smallint" minOccurs="0" />
<xsd:element name="tinyintcol" type="sqltypes:tinyint" minOccurs="0" />
<xsd:element name="bitcol" type="sqltypes:bit" minOccurs="0" />
<xsd:element name="nvarcharcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52" />
</xsd:simpleType>
</xsd:element>
<xsd:element name="varcharcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52" />
</xsd:simpleType>
</xsd:element>
<xsd:element name="ncharcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="charcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="varbinarycol" type="sqltypes:varbinary" minOccurs="0" />
<xsd:element name="moneycol" type="sqltypes:money" minOccurs="0" />
<xsd:element name="decimalcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:decimal">
<xsd:totalDigits value="13" />
<xsd:fractionDigits value="9" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="floatcol" type="sqltypes:float" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<complex xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2">
<id>1</id>
<sysnamecol>sysname column</sysnamecol>
<datetimecol>2009-04-29T06:47:25.407</datetimecol>
<bigintcol>100000000</bigintcol>
<intcol>1000000</intcol>
<smallintcol>1000</smallintcol>
<tinyintcol>100</tinyintcol>
<bitcol>1</bitcol>
<nvarcharcol>nvarchar column</nvarcharcol>
<varcharcol>varchar column</varcharcol>
<ncharcol>nchar column </ncharcol>
<charcol>char column </charcol>
<varbinarycol>dmFyYmluYXJ5IGNvbHVtbg==</varbinarycol>
<moneycol>123.4500</moneycol>
<decimalcol>67.890000000</decimalcol>
<floatcol>1.234567890123000e+005</floatcol>
</complex>
*/

This entry was posted on Wednesday, May 06, 2009 and is filed under . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments