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
    Posts
    • January 11, 2006 at 1:33 PM #12640
      community-content

        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

        [code:1:68335add5d]<xs:complexType>
        <xs:sequence>
        <xs:element name=\"mediciones\">
        <xs:complexType>
        <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\" />
        </xs:complexType>[/code:1:68335add5d]
        so I receive messages like this…
        [code:1:68335add5d] …
        <Medicion poder_calorifico=\"100\" co2=\"200\" energia=\"250\" />
        …[/code:1:68335add5d]
        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

      • January 17, 2006 at 3:54 AM #12641
        community-content

          Hello,
          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

          http://codebetter.com/blogs/jeff.lynch/articles/128664.aspx

          • January 17, 2006 at 3:14 AM #12642
            community-content

              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.

              • January 18, 2006 at 11:01 PM #12643
                community-content

                  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

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