none
Biztalk WCF-SQL polling sample using a FOR XML Path RRS feed

  • Question

  • I've been searching in the web for a sample that uses FOR XML "PATH" to poll the SQL database . The result returned from my query is a parent child data and FOR XML PATH is the best choice to structure it in that way . I guess I'm missing something while generating the schemas from this stored procedure.

    I guess Dan Rosanova has touched on this concept (http://social.technet.microsoft.com/wiki/contents/articles/3480.aspx) , but its using XML Auto. Again there is no sample available so makes things a bit difficult.

    Can someone point to a sample walkthrough , generating the schemas and then later using it in the application.

    Thanks

    Anthstone

     

    Wednesday, November 16, 2011 11:05 PM

Answers

  • I used XMLPolling and it worked for me. you can go for XMLPolling. Steps to be followed:

    1) Create the SP which will have the SELECT query similar to below:

    ;WITH XMLNAMESPACES (default 'http://yourcustomnamespace')
     Select * from Employee FOR XML PATH('YourCustomRootNode') 

    2) Create a schema out of the table using the following query

    Select * from Employee for xml auto, xmlschema 

    3) Re-name the root name and namespace as per you mentioned in point#1 (YourCustomRootNode)

    4) Create an Envelope Schema and refer the schema from point#3. Also make a note of the root node name and namespace that we need to specify

    in the admin console.

    5) Assign the Body XPath to debatch. Refer this.  Deploy the solution.

    6) In the Admin console, add the Root Node Name and namespace mentioned in point#4 under "XmlStoredProcedureRoodNodeName" and "XmlStoredProcedureRoodNodeNamespace"

    There you go. I did this for debatching. You can do for nomarl batch message instead of Envelope create a normal document schema.

    Thanks


    SKGuru
    Thursday, November 17, 2011 3:39 PM
  • -Create the stored procedure that returns xml (or xml part) by using the FOR XML PATH syntax

    -Setup a receive location using WCF-SQL. Select XmlPolling. Choose a rootname and namespace for the adapter to wrap around the xml returned from SQL (mandatory).

    -Set Polling Statement to: exec [SPNAME]

    -Set PollDataAvailableStatement to something appropriate that will return a count > 0 if there are rows/xml to be polled.

    -Use passthrureceive pipeline for the receive-location

    -Set up a send port (FILE) that subscribes to everything that comes from the receiveport used for the receivelocation.

    -Start the application. Examine the XML returned from the adapter.

    -In VS generate a schema using well-formed XML (Add->Add generated Items->Generate Schemas) (NOTE: You may have to run the InstallWFX.vbs found under the BizTalk SDK/Utilities/Schema generator, if you have not already done this earlier on the machine).

    -Choose the xml file generated by the adapter (give the file a name representing the schema you are trying to create).

    -Now you should have a schema representing the xml returned by the adapter, you may have to go through the schema manually and change data types to something more appropriate than what the wizard has chosen.

    Morten la Cour

    Thursday, November 17, 2011 10:42 AM

All replies

  • -Create the stored procedure that returns xml (or xml part) by using the FOR XML PATH syntax

    -Setup a receive location using WCF-SQL. Select XmlPolling. Choose a rootname and namespace for the adapter to wrap around the xml returned from SQL (mandatory).

    -Set Polling Statement to: exec [SPNAME]

    -Set PollDataAvailableStatement to something appropriate that will return a count > 0 if there are rows/xml to be polled.

    -Use passthrureceive pipeline for the receive-location

    -Set up a send port (FILE) that subscribes to everything that comes from the receiveport used for the receivelocation.

    -Start the application. Examine the XML returned from the adapter.

    -In VS generate a schema using well-formed XML (Add->Add generated Items->Generate Schemas) (NOTE: You may have to run the InstallWFX.vbs found under the BizTalk SDK/Utilities/Schema generator, if you have not already done this earlier on the machine).

    -Choose the xml file generated by the adapter (give the file a name representing the schema you are trying to create).

    -Now you should have a schema representing the xml returned by the adapter, you may have to go through the schema manually and change data types to something more appropriate than what the wizard has chosen.

    Morten la Cour

    Thursday, November 17, 2011 10:42 AM
  • I used XMLPolling and it worked for me. you can go for XMLPolling. Steps to be followed:

    1) Create the SP which will have the SELECT query similar to below:

    ;WITH XMLNAMESPACES (default 'http://yourcustomnamespace')
     Select * from Employee FOR XML PATH('YourCustomRootNode') 

    2) Create a schema out of the table using the following query

    Select * from Employee for xml auto, xmlschema 

    3) Re-name the root name and namespace as per you mentioned in point#1 (YourCustomRootNode)

    4) Create an Envelope Schema and refer the schema from point#3. Also make a note of the root node name and namespace that we need to specify

    in the admin console.

    5) Assign the Body XPath to debatch. Refer this.  Deploy the solution.

    6) In the Admin console, add the Root Node Name and namespace mentioned in point#4 under "XmlStoredProcedureRoodNodeName" and "XmlStoredProcedureRoodNodeNamespace"

    There you go. I did this for debatching. You can do for nomarl batch message instead of Envelope create a normal document schema.

    Thanks


    SKGuru
    Thursday, November 17, 2011 3:39 PM