InfoPath and native web services

InfoPath and native web services

• merrickchaffer

  Hi Lars,
  
  I want to first mention that this is a long post due to the amount details needed to ensure the proposed solution would be as close to what you need.  From the description you've mentioned, I would propose the following solution to you (may need a few tweaks to fit your exact scenario):

  • Create a SQL Function to retrieve the XML data.  Since you only need to retrieve one XML at a time, a SQL function would do the job perfectly.  This would be mapped to the Query portion of the InfoPath web service data source.
    Ie. create function GetXml (@a int)
       returns xml (myXmlSchema)
       begin
           declare @b xml (myXmlSchema)
           select @b = MyXml from MyXmlData where RowID = @a
           return @b
       end
  • Create a SQL Stored Procedure for the update portion.  This would be mapped to the Submit portion of the InfoPath web service data source.
    Ie. create PROC UpdateXml (@a int, @x xml(myXmlSchema) )
            as
            Update MyXmlData
            set MyXml = @x where RowID = @a

  < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  Please note that the update would completely override the data entry in the server with the new data from InfoPath.

   

  When creating the endpoint I recommend setting the FORMAT for the update stored procedure.

  CREATE ENDPOINT ETL_Metadata_EP
    STATE = STARTED
  AS HTTP (
    AUTHENTICATION = ( INTEGRATED ),
    PATH = '/metadata',
    PORTS = ( CLEAR ),
    SITE = '*'
  )
  FOR SOAP (
    WEBMETHOD 'GetSystemDefinition' (
      name='ETL_SYS.dbo.ETL_getSystemDefinition_fnc'
    ),
    WEBMETHOD 'SetSystemDefinition' (
      name='ETL_SYS.dbo.ETL_setSystemDefinition_SP', FORMAT=ROWSETS_ONLY
    ),
    WSDL = DEFAULT,
    SCHEMA = STANDARD,
    DATABASE = 'ETL_SYS',
    NAMESPACE = 'http://mygarbleddomain/schemas/system'
  );

  The main reason behind specifying the FORMAT for the update statement is that it provides a better mechanism for InfoPath to detect a failure during Submit.

   

  The following is not recommended, but is useful as a quick workaround for this purpose:

  • Download and save the WSDL document (as XML file) using IE or another web browser
  • http://servername/metadata wsdl
  • Open the saved WSDL file and search for “<xsd:element ref="xsd:schema" />”
  • Comment that out using <!-- --> or just remove this text
  • There should only be one entry of this

   

  At this point, you can design the InfoPath form pointing to the file (file:///C:/InfoPathApp/wsdl.xml) as the web service data source.  Select the appropriate method for Query and for Submit and you should be good to go.  I’ve tried this with InfoPath SP1.

   

  A couple of things to be aware of:

  • InfoPath doesn’t really like multiple independent XML schemas in the same XML schema collection

  Ie. CREATE XML SCHEMA COLLECTION myXmlSchema as N'<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:ns="http://yukonsoap" xmlns:xsd="http://www.w3.org/2001/XMLSchema"  targetNamespace="http://yukonsoap">

     <simpleType name="SOAPStruct">

       <restriction base="xsd:string" />

     </simpleType>

     <element name="soapelem" type="ns:SOAPStruct"/>

  </schema>

  <schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:ns="http://foobar" xmlns:xsd="http://www.w3.org/2001/XMLSchema"  targetNamespace="http://foobar">

     <element name="Body" type="ns:Body" />

     <complexType name="Body" >

       <sequence>

         <element name="hello" type="xsd:string" />

         <element name="test1" type="xsd:int" />

       </sequence>

       <anyAttribute namespace="##any" processContents="skip" />

     </complexType>

  </schema>'

  • The above SQL XML schema collection 2 independent XML schemas.  InfoPath only shows the structure for one of them in the “Data Source” pane.
  • When specifying the “Submit” parameter fields, for the XML data type parameter, please make sure the correct level of XML structure is specified.  It makes a difference as to whether the update will actually succeed or not depending on what you’ve selected for the “Include” drop down.  If you hit a failure during Submit, I would suggest switching the selection to see if that resolves the problem.

  I hope this solve your scenario.

   

  Regards,

  Jimmy

  
  

• Ian Bates

  Thanks Jimmy,
  
  To answer your questions:
  Will XML be the only data type the InfoPath app will be retrieving from SQL
  Yes, I will only be retrieving data stored in the XML datatype.
  
  Are all the XML data conforming to a specific set of XSD schema, or is the data any old random XML
  All the XML data conforms to a specific set of XSD schema (stored as a schema collection in the database).
  
  Is there a requirement to update the XML data on the SQL server using InfoPath, or is the InfoPath application strictly for data retrieval and display purposes
  The XML data is used to configure various processes in Integration Services and what I need is a way to easily modify the configuration, so yes, it is a requirement to be able to update data as well.
  
  Is the deployment of the InfoPath software the RTM version or SP1 or a mix of both
  Since the number of users using InfoPath will be small I can have them install whatever version is needed. I was running SP1 in the examples described in the earlier post.
  
  The XML columns are typed (schema bound) and I will be retrieving only one XML at a time, which is what the stored procedures accessible as a web service were doing.
  
  Many thanks,
  Lars
  
  

• swathi_challa

  Lasa,

  Custom WSDL can be a potential solution, but as you mentioned, it can be a bit daunting. I like to get a better understand of the scenario you are trying to solve in order to be able to provide a cleaner solution, without asking you to try a bunch of stuff. I know of a couple of possible solutions, but would prefer to start you down the proper path to begin with instead of multiple paths.

  You mentioned in your posting that your data is stored as XML in the database.

  • Will XML be the only data type the InfoPath app will be retrieving from SQL
  • Are all the XML data conforming to a specific set of XSD schema, or is the data any old random XML
  • Is there a requirement to update the XML data on the SQL server using InfoPath, or is the InfoPath application strictly for data retrieval and display purposes
  • Is the deployment of the InfoPath software the RTM version or SP1 or a mix of both

  If the XML data has a fixes set of structures, I would recommend creating a XSD schema for them and create the table column as schema bounded XML columns.  This adds the benefit of having SQL server verifying that all data inserted into the column conforms to the schema.  If you are then retrieving only one XML data at a time, it would make sense to use SQL functions that return XML.

  Thanks,
  Jimmy

  
  
• InfoPath and native web services