Promoting elements from SQL Adapter Schemas

by eliasen 17. december 2006 22:42

Hi

When generating a schema based on the SQL Adapter, there are some options for manipulating the XML.

I always use the "for xml auto, elements" version, because I'm an "element-kind-of-guy". Others use the "for xml auto" for several reasons:

  1. It is the default
  2. Attributes create smaller XML
  3. They like attributes

None the less, I'm an "element-kind-of-guy" and I do what I want! :-)

Anyway, the schemas that are the result of this leave my schema with loads of records instead of elements, meaning that I can not promote the result to distinguished fields.

There is a way of changing this, though... allthough I really don't consider it to be a nice solution :-)

As an example, I have the following table in a database:

And I have this stored procedure to get the values:

CREATE PROCEDURE [dbo].[Get_eliasen]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 SELECT * from eliasen for xml auto, elements, xmldata
END

Basically, it returns all rows from the table. The schema generated from this stored procedure will look like this:

The "seqno", "field1", "field" and "field3" are records and not elements. If I want to change them to be elements instead, I need to open the XSD in a text editor and do this manually:

As an example, I will change the record "seqno" into an elements.

The original XSD looks like this:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://eliasen.dk/SQLAdaterRecordsTest" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <msbtssql:sqlScript value="exec [Get_eliasen]" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="SQLAdaterRecordsTestRequest">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="eliasen" xmlns:q1="http://eliasen.dk/SQLAdaterRecordsTest" type="q1:eliasenType" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:complexType name="eliasenType">
    <xs:choice minOccurs="0" maxOccurs="unbounded">
      <xs:element name="seqno" xmlns:q2="http://eliasen.dk/SQLAdaterRecordsTest" type="q2:seqnoType" />
      <xs:element name="field1" xmlns:q3="http://eliasen.dk/SQLAdaterRecordsTest" type="q3:field1Type" />
      <xs:element name="field2" xmlns:q4="http://eliasen.dk/SQLAdaterRecordsTest" type="q4:field2Type" />
      <xs:element name="field3" xmlns:q5="http://eliasen.dk/SQLAdaterRecordsTest" type="q5:field3Type" />
    </xs:choice>
  </xs:complexType>
  <xs:complexType name="seqnoType">
    <xs:simpleContent>
      <xs:extension base="xs:long" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field1Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field2Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field3Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
</xs:schema>

And I will change it into this:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://eliasen.dk/SQLAdaterRecordsTest" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <msbtssql:sqlScript value="exec [Get_eliasen]" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="SQLAdaterRecordsTestRequest">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="eliasen" xmlns:q1="http://eliasen.dk/SQLAdaterRecordsTest" type="q1:eliasenType" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:complexType name="eliasenType">
    <xs:choice minOccurs="0" maxOccurs="unbounded">
      <xs:element name="seqno" type="xs:long" />
      <xs:element name="field1" xmlns:q3="http://eliasen.dk/SQLAdaterRecordsTest" type="q3:field1Type" />
      <xs:element name="field2" xmlns:q4="http://eliasen.dk/SQLAdaterRecordsTest" type="q4:field2Type" />
      <xs:element name="field3" xmlns:q5="http://eliasen.dk/SQLAdaterRecordsTest" type="q5:field3Type" />
    </xs:choice>
  </xs:complexType>
  <xs:complexType name="field1Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field2Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field3Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
</xs:schema>

So what have I done?

  1. I Found the definition of the record "seqno". It was based on the type "q2:seqnoType".
  2. I found where the "seqnoType" type was declared, and noted that it extended "xs:long".
  3. I changed the type of "seqno" from "q2:seqnoType" to be "xs:long".
  4. I removed the q2 namespace declaration from the seqno element, as it was no longer needed.
  5. I removed the "seqnoType" type declaration, as it was no longer needed.

That's it. Save the XSD and reload it in your schema editor - you will get this:

But it sure would be nice to have the option of just changing it in the properties of an elements in the schema editor...

Hope this helps someone.

EDIT on the 4'th of march 2007: Remember also that the record just below the root node must be set as "maxOccurs=1" instead of unbounded. Otherwise, you can not promote the fields below the record, obviously.

--
eliasen

Tags:

BizTalk 2006

Comments (4) -

Jan Eliasen
Jan Eliasen
10-10-2007 06:42:59 #

Hi all

Richard Seroter has a post regarding the same thing, basically - and he has a solution I didn't think of. Sp check out his post at seroter.wordpress.com/.../

--
eliasen

Reply

Bradley
Bradley
30-10-2007 15:06:33 #

This is exactly what I needed, however, when I change the maxOccurs to 1, the message does not route. Am I missing something?

Reply

Bradley
Bradley
30-10-2007 16:22:28 #

Never mind. This did work when I backed out all of my changes and tried again from scratch. Thanks for the article!

Reply

Jan Eliasen
Jan Eliasen
04-11-2007 11:06:02 #

Excellent. Sorry I didn't answer - I have been sick for the better part of a week now :-(

--
eliasen

Reply

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About the author

Jan Eliasen is 37 years old, divorced and has 2 sons, Andreas (July 2004) and Emil (July 2006).

Jan has a masters degree in computer science and is currently employed at Logica Denmark as an IT architect.

Jan is a 6 times Microsoft MVP in BizTalk Server (not currently an MVP) and proud co-author of the BizTalk 2010 Unleashed book.

BizTalk Server 2010 Unleashed


Buy from Amazon

Microsoft MVP


6 times: July 2004, July 2008, July 2009, July 2010, July 2011, and July 2012. Not currently an MVP.

MCTS

Image to show

Month List

Page List