none
WCF-SQL using FOR XML PATH, xml returned in <XML_GUID>

    Question

  • I am trying to call this stored proc

    ALTER PROCEDURE [dbo].[ibt_SelectCustomerData] ( @EdiUserId_EDUS varchar (10) ,@EdiBatchNumber_EDBT varchar (15) ,@EdiTransactNumber_EDTN varchar (22) ) AS BEGIN WITH XMLNAMESPACES('http://Illumina.BizTalk.EOne.CustomerSync' AS ns0) SELECT CAST(F550101Y.CAAN8 AS NUMERIC) 'ns0:CAAN8' ,RTRIM(F550101Y.CAALPH) 'ns0:CAALPH' ,RTRIM(F550101Y.CAAT1) 'ns0:CAAT1' ,RTRIM(F550101Y.CATAX) 'ns0:CATAX' ,LTRIM(RTRIM(F550101Y.CAMCU)) 'ns0:CAMCU' ,RTRIM(F550101Y.CACTTL) 'ns0:CACTTL' ,CAST(F550101Y.CAAN81 AS NUMERIC) 'ns0:CAAN81' ,CAST(F550101Y.CAAN82 AS NUMERIC) 'ns0:CAAN82' ,CAST(F550101Y.CAAN83 AS NUMERIC) 'ns0:CAAN83' ,CAST(F550101Y.CAAN84 AS NUMERIC) 'ns0:CAAN84' ,CAST(F550101Y.CAAN86 AS NUMERIC) 'ns0:CAAN86' ,CAST(F550101Y.CAAN85 AS NUMERIC) 'ns0:CAAN85' ,RTRIM(F550101Y.CAAC01) 'ns0:CAAC01' ,RTRIM(F550101Y.CAAC02) 'ns0:CAAC02' ,RTRIM(F550101Y.CAAC03) 'ns0:CAAC03' ,RTRIM(F550101Y.CAAC04) 'ns0:CAAC04' ,RTRIM(F550101Y.CAAC08) 'ns0:CAAC08' ,RTRIM(F550101Y.CAAC09) 'ns0:CAAC09' ,RTRIM(F550101Y.CAAC11) 'ns0:CAAC11' ,RTRIM(F550101Y.CAAC14) 'ns0:CAAC14' ,RTRIM(F550101Y.CAAC15) 'ns0:CAAC15' ,RTRIM(F550101Y.CAAC16) 'ns0:CAAC16' ,RTRIM(F550103Y.CSCO) 'ns0:CSCO' ,RTRIM(F550103Y.CSTRAR ) 'ns0:CSTRAR' ,CAST(F550103Y.CSARPY AS NUMERIC) 'ns0:CSARPY' ,RTRIM(F550103Y.CSCRCD ) 'ns0:CSCRCD' ,RTRIM(F550103Y.CSCRCA ) 'ns0:CSCRCA' ,RTRIM(F550103Y.CSACL ) 'ns0:CSACL' ,RTRIM(F550103Y.CSPOPN ) 'ns0:CSPOPN' ,RTRIM(F550103Y.CSEXR1 ) 'ns0:CSEXR1' ,RTRIM(F550103Y.CSTXA1 ) 'ns0:CSTXA1' ,RTRIM(F550103Y.CSBADT ) 'ns0:CSBADT' ,RTRIM(F550103Y.CSN8RE1) 'ns0:CSN8RE1' ,RTRIM(F550103Y.CSASN ) 'ns0:CSASN' ,RTRIM(F550103Y.CSINMG ) 'ns0:CSINMG' ,RTRIM(F550103Y.CSPLST ) 'ns0:CSPLST' ,RTRIM(F550103Y.CSHOLD ) 'ns0:CSHOLD' ,RTRIM(F550103Y.CSROUT ) 'ns0:CSROUT', ( SELECT RTRIM(F550102Y.CBADD1) 'ns0:CUSTOMERADDRESS/ns0:CBADD1' ,RTRIM(F550102Y.CBADD2) 'ns0:CUSTOMERADDRESS/ns0:CBADD2' ,RTRIM(F550102Y.CBADD3) 'ns0:CUSTOMERADDRESS/ns0:CBADD3' ,RTRIM(F550102Y.CBADD4) 'ns0:CUSTOMERADDRESS/ns0:CBADD4' ,RTRIM(F550102Y.CBCTY1) 'ns0:CUSTOMERADDRESS/ns0:CBCTY1' ,RTRIM(F550102Y.CBADDS) 'ns0:CUSTOMERADDRESS/ns0:CBADDS' ,RTRIM(F550102Y.CBADDZ) 'ns0:CUSTOMERADDRESS/ns0:CBADDZ' ,RTRIM(F550102Y.CBCTR ) 'ns0:CUSTOMERADDRESS/ns0:CBCTR' ,RTRIM(F550102Y.CBCOUN) 'ns0:CUSTOMERADDRESS/ns0:CBCOUN' FROM TRDTA.F550102Y F550102Y WHERE F550102Y.CBAN8 = F550101Y.CAAN8 AND F550102Y.CBEDBT = @EdiBatchNumber_EDBT AND (F550102Y.CBEDUS) = @EdiUserId_EDUS AND (F550102Y.CBEDTN) = @EdiTransactNumber_EDTN FOR XML PATH(''), type ) "ns0:CUSTOMERADDRESSES", ( SELECT RTRIM(F550104Y.CDIDLN) 'ns0:CUSTOMERPHONE/ns0:CDIDLN' ,RTRIM(F550104Y.CDCNLN) 'ns0:CUSTOMERPHONE/ns0:CDCNLN' ,RTRIM(F550104Y.CDRCK7) 'ns0:CUSTOMERPHONE/ns0:CDRCK7' ,RTRIM(F550104Y.CDPHTP) 'ns0:CUSTOMERPHONE/ns0:CDPHTP' ,RTRIM(F550104Y.CDAR1 ) 'ns0:CUSTOMERPHONE/ns0:CDAR1' ,RTRIM(F550104Y.CDPH1 ) 'ns0:CUSTOMERPHONE/ns0:CDPH1' FROM TRDTA.F550104Y F550104Y WHERE F550104Y.CDAN8 = F550101Y.CAAN8 AND F550104Y.CDEDBT = @EdiBatchNumber_EDBT AND (F550104Y.CDEDUS) = @EdiUserId_EDUS AND (F550104Y.CDEDTN) = @EdiTransactNumber_EDTN FOR XML PATH(''), type )"ns0:CUSTOMERPHONES", ( SELECT RTRIM(F550105Y.CEPA8 ) 'ns0:CUSTOMERADDRESSORG/ns0:CEPA8' ,RTRIM(F550105Y.CEOSTP) 'ns0:CUSTOMERADDRESSORG/ns0:CEOSTP' FROM TRDTA.F550105Y F550105Y WHERE F550105Y.CEAN8 = F550101Y.CAAN8 AND F550105Y.CEEDBT = @EdiBatchNumber_EDBT AND (F550105Y.CEEDUS) = @EdiUserId_EDUS AND (F550105Y.CEEDTN) = @EdiTransactNumber_EDTN FOR XML PATH(''), type )"ns0:CUSTOMERADDRESSORGS", ( SELECT RTRIM(F550106Y.CFIDLN) 'ns0:CUSTOMEREMAILADDRESS/ns0:CDIDLN' ,RTRIM(F550106Y.CFRCK7) 'ns0:CUSTOMEREMAILADDRESS/ns0:CDRCK7' ,RTRIM(F550106Y.CFETP) 'ns0:CUSTOMEREMAILADDRESS/ns0:CDPHTP' ,RTRIM(F550106Y.CFEMAIL) 'ns0:CUSTOMEREMAILADDRESS/ns0:CFEMAIL' FROM TRDTA.F550106Y F550106Y WHERE F550106Y.CFAN8 = F550101Y.CAAN8 AND F550106Y.CFEDBT = @EdiBatchNumber_EDBT AND (F550106Y.CFEDUS) = @EdiUserId_EDUS AND (F550106Y.CFEDTN) = @EdiTransactNumber_EDTN FOR XML PATH(''), type )"ns0:CUSTOMEREMAILADDRESSES", ( SELECT RTRIM(CHIDLN) 'ns0:CUSTOMERCONTACT/ns0:CHIDLN' ,RTRIM(CHTYC ) 'ns0:CUSTOMERCONTACT/ns0:CHTYC' ,RTRIM(CHMLNM) 'ns0:CUSTOMERCONTACT/ns0:CHMLNM' FROM TRDTA.F550108Y F550108Y WHERE F550108Y.CHAN8 = F550101Y.CAAN8 AND F550108Y.CHEDBT = @EdiBatchNumber_EDBT AND (F550108Y.CHEDUS) = @EdiUserId_EDUS AND (F550108Y.CHEDTN) = @EdiTransactNumber_EDTN FOR XML PATH(''), type )"ns0:CUSTOMERCONTACTS" FROM TRDTA.F550101Y F550101Y ,TRDTA.F550103Y F550103Y WHERE (F550101Y.CAEDBT)=@EdiBatchNumber_EDBT AND (F550101Y.CAEDUS) = @EdiUserId_EDUS AND (F550101Y.CAEDTN) = @EdiTransactNumber_EDTN AND (F550103Y.CSEDBT)=@EdiBatchNumber_EDBT AND (F550103Y.CSEDUS) = @EdiUserId_EDUS AND (F550103Y.CSEDTN) = @EdiTransactNumber_EDTN AND F550101Y.CAAN8 = F550103Y.CSAN8 FOR XML PATH('ns0:Customer'), ROOT('ns0:CustomerSync'),ELEMENTS END

    using the WCF-SQL adapter. I get back the xml wrapped in a <XML_GUID> node

    <ibt_selectcustomerdataresponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"> <storedprocedureresultset0><storedprocedureresultset0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/ibt_SelectCustomerData"><xml_f52e2b61-18a1-11d1-b105-00805f49916b><ns0:CustomerSync xmlns:ns0="http://Illumina.BizTalk.EOne.CustomerSync"><ns0:Customer><ns0:CAAN8>5463</ns0:CAAN8><ns0:CAALPH>Fred Smith</ns0:CAALPH><ns0:CAAT1>INC</ns0:CAAT1><ns0:CATAX></ns0:CATAX><ns0:CAMCU>250</ns0:CAMCU><ns0:CACTTL>NA</ns0:CACTTL><ns0:CAAN81>3278</ns0:CAAN81><ns0:CAAN82>33</ns0:CAAN82><ns0:CAAN83>3378</ns0:CAAN83><ns0:CAAN84>338</ns0:CAAN84><ns0:CAAN86>378</ns0:CAAN86><ns0:CAAN85>3379</ns0:CAAN85><ns0:CAAC01>C</ns0:CAAC01><ns0:CAAC02>TX</ns0:CAAC02><ns0:CAAC03>USW</ns0:CAAC03><ns0:CAAC04>AMR</ns0:CAAC04><ns0:CAAC08></ns0:CAAC08><ns0:CAAC09>STD</ns0:CAAC09><ns0:CAAC11></ns0:CAAC11><ns0:CAAC14></ns0:CAAC14><ns0:CAAC15></ns0:CAAC15><ns0:CAAC16></ns0:CAAC16><ns0:CSCO>00000</ns0:CSCO><ns0:CSTRAR></ns0:CSTRAR><ns0:CSARPY>3379</ns0:CSARPY><ns0:CSCRCD>USD</ns0:CSCRCD><ns0:CSCRCA>USD</ns0:CSCRCA><ns0:CSACL>0</ns0:CSACL><ns0:CSPOPN>JHERNANDEZ</ns0:CSPOPN><ns0:CSEXR1>S</ns0:CSEXR1><ns0:CSTXA1>E</ns0:CSTXA1><ns0:CSBADT>B</ns0:CSBADT><ns0:CSN8RE1>7</ns0:CSN8RE1><ns0:CSASN>STANDARD</ns0:CSASN><ns0:CSINMG></ns0:CSINMG><ns0:CSPLST>N</ns0:CSPLST><ns0:CSHOLD></ns0:CSHOLD><ns0:CSROUT></ns0:CSROUT><ns0:CUSTOMERADDRESSES><ns0:CUSTOMERADDRESS xmlns:ns0="http://Illumina.BizTalk.EOne.CustomerSync"><ns0:CBADD1>2130 WOODWARD</ns0:CBADD1><ns0:CBADD2/><ns0:CBADD3/><ns0:CBADD4/><ns0:CBCTY1>AUSTIN</ns0:CBCTY1><ns0:CBADDS>TX</ns0:CBADDS><ns0:CBADDZ>78744-1832</ns0:CBADDZ><ns0:CBCTR/><ns0:CBCOUN/></ns0:CUSTOMERADDRESS></ns0:CUSTOMERADDRESSES><ns0:CUSTOMERPHONES><ns0:CUSTOMERPHONE xmlns:ns0="http://Illumina.BizTalk.EOne.CustomerSync"><ns0:CDIDLN>0</ns0:CDIDLN><ns0:CDCNLN>0</ns0:CDCNLN><ns0:CDRCK7>1</ns0:CDRCK7><ns0:CDPHTP/><ns0:CDAR1>512</ns0:CDAR1><ns0:CDPH1>243-0435</ns0:CDPH1></ns0:CUSTOMERPHONE></ns0:CUSTOMERPHONES><ns0:CUSTOMERCONTACTS><ns0:CUSTOMERCONTACT xmlns:ns0="http://Illumina.BizTalk.EOne.CustomerSync"><ns0:CHIDLN>0</ns0:CHIDLN><ns0:CHTYC/><ns0:CHMLNM>smith 1</ns0:CHMLNM></ns0:CUSTOMERCONTACT></ns0:CUSTOMERCONTACTS></ns0:Customer><ns0:Customer><ns0:CAAN8>89</ns0:CAAN8><ns0:CAALPH>Joe Fr</xml_f52e2b61-18a1-11d1-b105-00805f49916b> </storedprocedureresultset0></storedprocedureresultset0><returnvalue>0</returnvalue> </ibt_selectcustomerdataresponse>

    I have tried setting the inboundOperationType to XMLPolling and I get the same result.

    Any ideas?


    • Edited by MillaT Wednesday, February 08, 2012 8:56 PM
    Wednesday, February 08, 2012 6:35 PM

Answers

  • If the SP is already returning XML, the WCF SQL adapter should have the InboundOperationType set to XmlPolling.

    This will require an additional root and namespace to be wrapped around the XML by the adapter, since SQL XML is not garanteed well-formed.

    Also generating a schema in this case is a bit more tricky, and requires using

    some of the schema generated by SQL (xmldata or xmlschema) and some copy/pasting to include it inside the new root element. What you can do then is generate some sample XML by setting up the adapter to use xmlpolling and sending the output to a file folder (by using passthrureceive pipeline, and a file send port), and then generate a schema in VS using the sample XML.

    Note: You will still need to go through the generated schema an change data types where needed, since the auto-generation tool is terrible at "guessing" the correct type.  

    Morten la Cour



    Thursday, February 09, 2012 7:15 AM

All replies

  • Yes if you use the wcf-sql apater for with for xml path you will get XML in this way only..

    If you need to get the schema for your stored procedure you need to do following steps.

    -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 .

    -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).

    • Edited by sriaug Wednesday, February 08, 2012 6:48 PM
    Wednesday, February 08, 2012 6:47 PM
  • Your Xml packed in this <XML_F52E2B61-18A1-11d1-B105-00805F49916B.. envelope. Your Xml is inside this node. You can extract it easily.

    BTW Why do you transform your data to XML inside SQL code??

    I would suggest to receive the data from WCF-SQL as it is, then map it to required format. It would be much-much easier. Making this mapping in SQL is not the right place.


    Leonid Ganeline [BizTalk MVP] BizTalkien: Advanced Questions: have fun - test your knowledge

    Wednesday, February 08, 2012 7:37 PM
    Moderator
  • Your Xml packed in this <XML_F52E2B61-18A1-11d1-B105-00805F49916B.. envelope. Your Xml is inside this node. You can extract it easily.

    BTW Why do you transform your data to XML inside SQL code??

    I would suggest to receive the data from WCF-SQL as it is, then map it to required format. It would be much-much easier. Making this mapping in SQL is not the right place.


    Leonid Ganeline [BizTalk MVP] BizTalkien: Advanced Questions: have fun - test your knowledge

    As you can see by looking at the Stored proc there are four nested select statements.

    If there is a way for the adapter to handle it without changing those nested records to CDATA I would love to hear it.

    Wednesday, February 08, 2012 8:16 PM
  • I think there are the ways.

    If the WCF-SQL Wizard will not create the right schemas with nested records, try workarounds.

    See this topic or thistopic

    Or this one.


    Leonid Ganeline [BizTalk MVP] BizTalkien: Advanced Questions: have fun - test your knowledge

    Wednesday, February 08, 2012 9:20 PM
    Moderator
  • I guess the real question is why would Microsoft not bring over the same functionality from something that worked with the standard SQL adapter to the WCF-SQL adapter?
    Wednesday, February 08, 2012 9:58 PM
  • Hi,

    If you want correct schemas to be generated instead of xml wrapped in guid then don't use for xml.

    WCF-SQL adapter will generate the correct schemas for your procedure.


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/

    Thursday, February 09, 2012 5:01 AM
  • Hi,

    If you want correct schemas to be generated instead of xml wrapped in guid then don't use for xml.

    WCF-SQL adapter will generate the correct schemas for your procedure.


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/

    Not when you are using nested sql it won't.
    Thursday, February 09, 2012 5:06 AM
  • If the SP is already returning XML, the WCF SQL adapter should have the InboundOperationType set to XmlPolling.

    This will require an additional root and namespace to be wrapped around the XML by the adapter, since SQL XML is not garanteed well-formed.

    Also generating a schema in this case is a bit more tricky, and requires using

    some of the schema generated by SQL (xmldata or xmlschema) and some copy/pasting to include it inside the new root element. What you can do then is generate some sample XML by setting up the adapter to use xmlpolling and sending the output to a file folder (by using passthrureceive pipeline, and a file send port), and then generate a schema in VS using the sample XML.

    Note: You will still need to go through the generated schema an change data types where needed, since the auto-generation tool is terrible at "guessing" the correct type.  

    Morten la Cour



    Thursday, February 09, 2012 7:15 AM