locked
WCF-SQL Using Composite Operation to call a select statement - processing the results. RRS feed

  • Question

  • Hi

    I'm using a Composite Operation to preform multiple SQL Select Statements.

    My CompositeOperation Schema has a results entry and a DataSet with entries of "<Any>"

    The <Any> is there (I assume) because it doesn't know which fields have been specified in the SQL Select statement.

    The actual response from the CompositeOperation contains a definition of a schema to hold the results of the SQL Statement, 3 fields, Manufacturer_Name, Manufacturer_Product_Code and Status.

    <RequestResponse xmlns="http://SQL.CompositeOperation">
    <GetMasterProductDetailsResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
    <GetMasterProductDetailsResult>
    <DataSet xmlns="http://schemas.datacontract.org/2004/07/System.Data">
    <xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element msdata:IsDataSet="true" name="NewDataSet">
    <xs:complexType>
    <xs:sequence>
    <xs:element minOccurs="0" maxOccurs="unbounded" name="NewTable">
    <xs:complexType>
    <xs:sequence>
    <xs:element minOccurs="0" name="Manufacturer_Name" type="xs:string" />
    <xs:element minOccurs="0" name="Manufacturer_Product_Code" type="xs:string" />
    <xs:element minOccurs="0" name="Status" type="xs:string" />
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:schema>
    <diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet xmlns="">
    <NewTable>
    <Manufacturer_Name>2-Power</Manufacturer_Name>
    <Manufacturer_Product_Code>0957-2286-OEM</Manufacturer_Product_Code>
    <Status>Available</Status>
    </NewTable>
    </NewDataSet>
    </diffgr:diffgram>
    </DataSet>
    </GetMasterProductDetailsResult>
    <ReturnValue>0</ReturnValue>
    </GetMasterProductDetailsResponse>

    We have an explicit schema to hold the response with the 3 fields (that's simple) and in the Orchestration, we're using XPATH to parse the CompositeOperation response and extract the values (one row at a time, one column at a time), and then build a XML Document for further  processing. This does work OK - but it's a little cumbersome, and a pain when you need to add an extra column to the select statement, etc, etc.

    Is there a slick way of processing the CompositeOperation response into a useful schema?

    Thank

    Trefor

     

    Wednesday, September 5, 2018 11:20 AM

Answers

  • I solved this problem as follows.

    1. In the orchestration, I parsed the XML response above, and extracted the first instance of

    <xs:element minOccurs="0" name="Manufacturer_Name" type="xs:string" />
    <xs:element minOccurs="0" name="Manufacturer_Product_Code" type="xs:string" />
    <xs:element minOccurs="0" name="Status" type="xs:string" />

    And put it in a new XML message (with some headers, etc.)

    2. Then I parsed the XML response for occurrences of

    <NewTable>
    <Manufacturer_Name>2-Power</Manufacturer_Name>
    <Manufacturer_Product_Code>0957-2286-OEM</Manufacturer_Product_Code>
    <Status>Available</Status>
    </NewTable>
    

    and added that into my new XML message.

    So I've ended up with both the field names and the field values.

    It may not be beautiful, but it works.

    • Marked as answer by TreforE Thursday, October 25, 2018 12:39 PM
    Thursday, October 25, 2018 12:39 PM

All replies

  • Hi,

    There are two types of schema response you can get one is Typed Schema and other is UnTyped Schema (Refer Link#1),

    So I would suggest to keep schema structure like

    Input Schema > Canonical Schema (BizTalk Internal schema) > Output Schema

    Now in case of Typed schema you can create the Canonical Structure of Schema similar to one you get in Response of WCF-SQL, then in case of adding extra column you only need to generate the proxy and refresh the schema assembly reference into your Orchestration Project.

    Link#1 https://psrathoud.wordpress.com/tag/wcf-sql/

    HTH


    Hope this Helps!!!! Regards, Note: Please Mark As Answered if you satisfy with Reply.

    Thursday, September 6, 2018 8:23 AM
  • I solved this problem as follows.

    1. In the orchestration, I parsed the XML response above, and extracted the first instance of

    <xs:element minOccurs="0" name="Manufacturer_Name" type="xs:string" />
    <xs:element minOccurs="0" name="Manufacturer_Product_Code" type="xs:string" />
    <xs:element minOccurs="0" name="Status" type="xs:string" />

    And put it in a new XML message (with some headers, etc.)

    2. Then I parsed the XML response for occurrences of

    <NewTable>
    <Manufacturer_Name>2-Power</Manufacturer_Name>
    <Manufacturer_Product_Code>0957-2286-OEM</Manufacturer_Product_Code>
    <Status>Available</Status>
    </NewTable>
    

    and added that into my new XML message.

    So I've ended up with both the field names and the field values.

    It may not be beautiful, but it works.

    • Marked as answer by TreforE Thursday, October 25, 2018 12:39 PM
    Thursday, October 25, 2018 12:39 PM