Return Multiple Columns From a SQL Table

[RitaG]

Hello.

I'm able to return a single column (defined as XML) into BizTalk 2006 from a
SQL 2005 table using the "Any" data type in a schema (a Stored Procedure is
executed from a "Receive Location"). However, I now need to return the XML
column along with a couple of columns and cannot figure out how to do it.

Any suggestions will be greatly appreciated!

Thanks,
Rita

[Brian Jones]

Rita,

Why not use the Schema Generation Wizard in BTS 2006 and let BizTalk create
a schema for you? This way, you can either define the SQL Statement or
Stored Procedure to execute and half your battle is resolved. Certainly,
you will have to ensure your SQL or SPROC returns XML, and during the Schema
Generation you will have to append your SQL with XMLDATA. For Example: FOR
XML AUTO, XMLDATA. This information is used to generate the actual XML
Schema.

HTH

[RitaG]

Hi Brian,

Many thanks for your suggestion. I was able to get it to work correctly.
I'm only into using BizTalk for six weeks now and have lots to learn!

Rita

Post by Brian Jones
Rita,
Why not use the Schema Generation Wizard in BTS 2006 and let BizTalk create
a schema for you? This way, you can either define the SQL Statement or
Stored Procedure to execute and half your battle is resolved. Certainly,
you will have to ensure your SQL or SPROC returns XML, and during the Schema
Generation you will have to append your SQL with XMLDATA. For Example: FOR
XML AUTO, XMLDATA. This information is used to generate the actual XML
Schema.
HTH
--
Brian Jones
http://bajwork.blogspot.com

[Brian Jones]

Glad I could help. Don't worry... I've been doing it since 2004 and I'm
still learning. :)

[Jan Eliasen]

Post by Brian Jones
Glad I could help. Don't worry... I've been doing it since 2004 and I'm
still learning. :)

I have you beaten by a year! .-)