Sunday, December 17, 2006

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

Sunday, December 17, 2006 11:42:35 PM (Romance Standard Time, UTC+01:00)  #    Comments [4]  | 
Wednesday, October 10, 2007 8:42:59 AM (Romance Daylight Time, UTC+02:00)
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 http://seroter.wordpress.com/2007/10/09/how-to-distinguish-biztalk-schema-record-nodes/

--
eliasen
Tuesday, October 30, 2007 4:06:33 PM (Romance Standard Time, UTC+01:00)
This is exactly what I needed, however, when I change the maxOccurs to 1, the message does not route. Am I missing something?
Tuesday, October 30, 2007 5:22:28 PM (Romance Standard Time, UTC+01:00)
Never mind. This did work when I backed out all of my changes and tried again from scratch. Thanks for the article!
Sunday, November 4, 2007 12:06:02 PM (Romance Standard Time, UTC+01:00)
Excellent. Sorry I didn't answer - I have been sick for the better part of a week now :-(

--
eliasen
All comments require the approval of the site owner before being displayed.
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, sup, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview

Theme design by Jelle Druyts