Build Xml document from SQL Query using For Xml clause

Question

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 "

 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;

gets this result

<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="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="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="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>

i need to refer to another online schema .. is that possible ?

Thanks.

Jordão · Accepted Answer · 2012-06-03 14:43:39Z

1

Since you need your query to adhere to an existing schema, I think you should take control of the generated XML yourself by using, e.g., EXPLICIT mode. You can't use the XMLSCHEMA option in this case, but it's OK since you already have your own schema.

answered Jun 3, 2012 at 14:43

Jordão

56.8k13 gold badges118 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dotmido Over a year ago

i used AUTO mode but i add namespaces and all prefixes inside the query hard-coded .. its really huge query but i don have another solution, what do u mean by using EXPLICIT mode, i was looking for a way to add the exist xml schema coz i donno how to add it actually within the query, i used WITH XMLNAMESPACES clause but its still poor result

Jordão Over a year ago

The idea is that you'd use EXPLICIT mode and create the XML that conforms to your XSD by hand. In that case, you don't need the query to know about your XSD.

Collectives™ on Stack Overflow

Build Xml document from SQL Query using For Xml clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related