How to call an store procedure for each node of an xml?

Home Page Forums BizTalk 2004 – BizTalk 2010 How to call an store procedure for each node of an xml?

Viewing 1 reply thread
  • Author
    • #12640

      Hi, I’m new in biztalk and I need some help to resolve this problem…

      I’m working with the biztalk sql adapter and with an xsd similar to the following

      <xs:element name=\"mediciones\">
      <xs:attribute name=\"poder_calorifico\" type=\"xs:string\" use=\"required\" />
      <xs:attribute name=\"c02\" type=\"xs:string\" use=\"required\" />
      <xs:attribute name=\"energia\" type=\"xs:decimal\" use=\"required\" />
      so I receive messages like this…
      [code:1:68335add5d] …
      <Medicion poder_calorifico=\"100\" co2=\"200\" energia=\"250\" />
      Then I need to call 3 times thee same sp with different parameters

      [b:68335add5d]1. sp(poder_calorifico, 100)
      2. sp(co2, 200)
      3. sp(energia, 250)[/b:68335add5d]
      How can I do this?

      Do I need to write a custom receive pipeline or I can resolve this in an orchestration?

      Your help will be appreciated. Thanks in advance

      Mauricio. Bs. As – Argentina

    • #12641

      You can loop/iterate through nodes in an XML message using the loop shape in BizTalk. You just need to supply the XPATH statement that will isolate the nodes to loop through. You could call your stored procedures on each node. I think it would probably help to promote the values you are looking for or use the mapper when creating the message used to call the stored procedure.

      I would personally recommend either using updategrams or perhaps OPENXML. I have found that a combination of OPENXML in my stored procedures and \”FOR XML AUTO\” select statements can give you more flexibility than updategrams. Good luck

      • #12642

        I don’t know that much about the SQL adapter. But, I think you would need to break this up into 3 separate messages to make 3 calls to the SQL.

        Hope this helps.

        • #12643

          You can send the entire XML to the stored procedure, then parse it in the stored proc using \”OpenXML\” (which basically parses the string into an XML Document object and then uses the xpath inside the stored proc to process it). Just do a google search or SQL Books Online on \”OpenXML\” to learn more. We actually used it for the first time about two months ago.

          Neal Waltes

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.