Build Xml document from SQL Query using For Xml clause

[jarlaxe]

I'm using AdventureWorks Database and I'd like to build xml block using query with FOR XML clause but i need to specify specific XML Schema in the result to have its datatypes and so on..e.g. this sample query " copied "\[code\] SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON c.ContactID = e.ContactID WHERE c.FirstName = 'Rob' FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL, XMLSCHEMA; \[/code\]gets this result\[code\]<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" 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="Employee"> <xsd:complexType> <xsd:sequence> <xsd:element name="EmployeeID" type="sqltypes:int" nillable="1" /> <xsd:element name="FirstName" nillable="1"> <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="http://stackoverflow.com/questions/10771966/50" /> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="MiddleName" nillable="1"> <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="http://stackoverflow.com/questions/10771966/50" /> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="LastName" nillable="1"> <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="http://stackoverflow.com/questions/10771966/50" /> </xsd:restriction> </xsd:simpleType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema> <Employee xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"> <EmployeeID>4</EmployeeID> <FirstName>Rob</FirstName> <MiddleName xsi:nil="true" /> <LastName>Walters</LastName> </Employee> <Employee xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"> <EmployeeID>168</EmployeeID> <FirstName>Rob</FirstName> <MiddleName>T</MiddleName> <LastName>Caron</LastName> </Employee></Employees>\[/code\]i need to refer to another online schema .. is that possible ?Thanks.