How to transform a flat SQL result set into a nested xml structure in BizTalk

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › How to transform a flat SQL result set into a nested xml structure in BizTalk

Viewing 2 reply threads
  • Author
    Posts
    • July 13, 2010 at 7:43 AM #25220
      community-content

        Hi,

        I’m using BizTalk 2009 and SQL Server 2008.

        What I need to do is to execute a query joining multiple DB tables e.g. 

        “SELECT * FROM CUSTOMER C INNER JOIN CUST_ADDRESS CA ON C.ID=CA.CID INNER JOIN CUST_PHONE CP ON C.ID= CP.CID”

         

        and transforming it into a schema like:

        <Cutomer>

        <ID></ID>

        <NAME></NAME>

            <Address>

            <street></street>

            <City></City>

             </Address>

             …

        </Customer>

         

        When executing this query – using WCF SQL adapter – it  will result in a flat (tabular) result set and the generated response schema will be flat

        How can I map it to the structured schema and group the child records (e.g. address) under the corresponding customer record

         

        Thanks,

        Yusuf.

         

         

      • July 13, 2010 at 1:44 PM #25228
        community-content

          Hi Yusuf,

           

             You would use the looping functoid in a map, join the left-hand side to the top-level node of your source schema, join the right-hand side to the Address node in your destination schema, make sure your Address node’s Max Occurs property is set to unbounded as the value. Then just link the fields in the source schema to the fields in the destination schema to map the data.

           

             Daniel.

           

        • July 13, 2010 at 3:27 PM #25230
          community-content

            Hi Yusuf.

            I work a lot with XMLs and Stored Procedures + WCF-SQL adapter.

            1. Here’s a link – it can help you to get structured xml on select  http://blogs.microsoft.co.il/blogs/yonathanm/archive/2009/11/24/XQuery-samples.aspx

            2. Why don’t you create a stored procedure and than just generate schemas with WCF? (Add generated items – consume adapter serivce)?

            You don’t even need a map – or if you do need, you will have to map Structured XML to structured XML, or to use XQuery i your stored procedure.

             

            • July 14, 2010 at 7:59 AM #25235
              community-content

                Thank you guys

                I went with the for xml query option and it seems working fine. 

                Thanks,

                Yusuf.

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