How to create Biztalk schema from FOR XML EXPLICIT

It's very straightforward to create Biztalk schema for SQL adapter if we use FOR XML AUTO. But we'll come across that FOR XML AUTO is not enough to get data from SQL, so that we use FOR XML EXPLICIT. In this case we have to follow some additional things to get biztalk schema generated properly. I''l go step-by-step with an example. This situation really came to me that's why I motivated to do this post.

1. Assume my SP,BIZTK_GetPPS300Attributes @pono, will generates required XML data set with FOR XML EXPLICIT.


2. Open Visual Studio and switch to Biztalk Explorer.
   Create a Receive Port.
   Create a Receive Location for that port and set following properties
   
   Set Address(URI) property as following
   
   Value of Document Root Element Name will be Root Element of schema.
   Document Target Namespace will be schema's target namespace
   For this example SQL Command will be exe BIZTK_GetPPS300Attributes 12345
   


3. Create a send port and set following properties
   


4. Enable Receive Location and start Send port by right clicking on them.


5. Now receive location will execute SQL command that you set and save the XML file in the location where you set in step 3. At this stage we dont want to validate against schema so that we set send/receive pipe line to PassThru.


6. Go to your biztalk project, select Generated Schemas option from Add Generated Items dialog box, give the XML file just you created.


7. Cool. Biztalk schema is properly generated.