Biztalk Sql Adapter

Question

is it possible to have sql script with "for xml path", instead of "for xml auto" for SQL Adapter?

for example:
select field1, field2 from PurchaseOrder
FOR XML
PATH('Customer'), ROOT('PurchaseOrder')

I always get syntax error while I am generating that schema by SQL Adapter

Answer 1

Hi,

Look at this post regarding FOR XML PATH http://www.modhul.com/2008/01/23/an-easier-way-to-do-complex-for-xml-explicit/.

Regards,

Steef-Jan Wiggers
MCTS BizTalk Server
http://soa-thoughts.blogspot.com/
If this answers your question please mark it accordingly

---

BizTalk

Answer 2

Hi Arthur. Yes you can, but the old SQL adapter on the receive side requires you to configure the 'Document Root Element Name' and the 'Document Target Namespace' values. So in your case I would leave the , ROOT('PurchaseOrder') part out of the SQL statement and use PurchaseOrder as the value for the 'Document Root Element Name' in the SQL adapter configuration on your receive location. The adapter will wrap the Customer element and everything else around a PurchaseOrder element on the way in. Also populate the 'Document Target Namespace' property with a proper namespace and the adapter will use that as the namespace for the message. Then in BizTalk have a schema that has PurchaseOrder as the root and the same target namespace that conforms to what you're building in SQL.

Regards,

---

Thiago Almeida - http://connectedthoughts.wordpress.com

Answer 3

Here is my script

select ExamId as [Exam/ExamId]
 from
ExamTable
where ExamId = 1234
FOR XML PATH('OUR')

but still get syntax error

Answer 4

It works fine for me if I create a table called ExamTable with a column ExamId. Can you please give more details on your problem? What is the exact error you get (copy and paste), and where do you get it - is the error in a message from BizTalk in the event log, or in SQL Management Studio?

Regards,
Thiago

---

Thiago Almeida - http://connectedthoughts.wordpress.com

Answer 5

it pops up this error message, after I click [Next] button from the SQL Transport Schema Generation Wiazrd

"Failed to execute SQL Statement. Please ensure that the supplied syntax is correct."

Answer 6

I just try to add a generated schema from sql adapter
so I add new generated items from visual studio 2008
and input related sql server, login name, pwd , database name information
also target namespace and document root element name
then input that sql script above, click [Next] button

finally that error message comes up

Answer 7

Ahh right. You shouldn't use the 'add generated schema' wizard. You have to craete a schema separately as if it was any other normal schema that conforms to the root node name you configure o the SQL receive port and the rest of what you're building in your SQL stored procedure, and treat this schema as any other schema inside BizTalk. Then configure a receive port like I mentioned on my original response. That's what I used to do to be able to use PATH in XML coming from SQL.

Regards,
Thiago

---

Thiago Almeida - http://connectedthoughts.wordpress.com

Answer 8

Hi Arthur,
I think you cannot use xml path in BizTalk add generated items wizard. Because it supports only xml auto format only.

Because of this only you'll get  this error "Failed to execute SQL Statement. Please ensure that the supplied syntax is correct".

Regards,
Elango