Building a M3 Web Service over an API and SQL Server's FOR XML

As part of the series of posts on agile interface development with ION there are a couple of foundation aspects we need:

• Building a M3 Web Service; and
• Creating the XML file for for the Web Service

Building a M3 Web Service over APIs

I've covered building a M3 web service over M3 display screens before and building these over APIs is fundamentally the same, but to recap briefly there are two basic steps to this:

1. Create the Web Service in MWS Designer

Once you've determined the API transaction you want to use (in this example I'll use MMS025MI.AddAlias) build the web service in MWS designer by:

Creating a new web service

and specify the API transaction to use

Check the aliases on the fields for naming conflicts and resolve as required.  I've seen this as an issue with, for example, some Warning message fields where these often do not have unique aliases.

Once it's set up then deploy the web service.

This will have published the SOAP web service to your MWS service.  From here any SOAP compliant application can call the web service to interact with M3.

2. Validate the Web Service

From LCM, manage the MWS application.  From the List function you can then find your deployed web service.  Copy the http link for the default router.

To validate the web service works correctly and to get the exact format of the XML file required by the web service, I tend to use SOAPUI.  

In SOAPUI create a new web service

then manipulate the default request to reflect the call you want to make:

becomes

Execute the test and validate that the data has been posted into M3. 

The XML above is the template for the file we will create below.  We want everything apart from the <soapenv> tags so:

  <AddAlias>   
   <AddAliasItem>   
    <Company>100</Company>   
    <AliasCategory>2</AliasCategory>   
    <AliasQualifier>EA13</AliasQualifier>   
    <ItemNumber>  02010</ItemNumber>   
    <AliasNumber>4434567001143</AliasNumber>   
    <AliasType>EA13</AliasType>   
   </AddAliasItem>   
  </AddAlias>   

Note that when using SOAPUI, if your MWS is on the Grid then you'll use the authentication mechanism setup within LCM for this.  If your MWS is pre-Grid then you'll normally pass in the authentication as part of the XML string.  For this example I used MWS on the Grid which requires the authentication to be added via the Auth tab at the bottom of the SOAPUI screen.

Creating the XML file for for the Web Service

The starting point for this is a SQL Server table that has the field names and formats exactly the same as the Aliases in the M3 Web Service.  Note however that a date for M3 should be in the format YYYY-MM-DD.  It's better to use SQL type char(10) for this rather than datetime.

There are many articles showing how to use FOR XML to create XML output.  I won't reinvent the wheel here and will instead just jump straight to the solution we developed:

 declare @xml xml                    --Used to build the initial XML record  
 declare @output varchar(max);     --Used for the final XML record  
 --Obtain the inner data  
 WITH XMLNAMESPACES (   
 DEFAULT '""http://your.company.net/MMS025MI/AddAlias""')            
 select @xml = (  
 select   
 *  
 FROM [dbo].AddAlias AddAliasItem       
 FOR XML AUTO , ELEMENTS,root('AddAlias') )       
 --Fix the quotes around the namespace  
 select @output = (select convert(varchar(max),@xml))  
 set @output = REPLACE ( @output , '"' , '"')  
 declare @cmd varchar(8000)  
 select @cmd = 'bcp "select ''' + @output + '''" queryout "C:\OUTPATH\data.xml"'  
      + ' -S SQLSERVERNAME -T -r -w -t -c -C RAW';  
 exec xp_cmdshell @cmd, NO_OUTPUT;  

Looking at the components of this:

 WITH XMLNAMESPACES (    
  DEFAULT '""http://your.company.net/MMS025MI/AddAlias""')  

This adds the XML namespace http://your.company.net/MMS025MI/AddAlias to the AddAlias node.

 select    
  *   
  FROM [dbo].AddAlias AddAliasItem  

This changes the node wrapping the actual data to AddAliasItem instead of AddAlias which is the table name.

 FOR XML AUTO , ELEMENTS,root('AddAlias') )   

This does two things.  Firstly by default FOR XML AUTO will treat the data as XML attributes on the AddAlias node.  Specifying ELEMENTS makes these appear as XML elements rather than attributes.  Secondly, while we renames the AddAlias node to AddAliasItem above, we still need an outer AddAlias node.  The command root('AddAlias') will add an outer AddAlias node wrapping the AddAliasItem node.

 select @output = (select convert(varchar(max),@xml))   
 set @output = REPLACE ( @output , '"' , '"')  

The XML output from the FOR XML AUTO command will convert quote marks (") to &quot;.  ION however requires that quote marks appear as quote marks, so we use the SQL REPLACE command to find and correct these.

 select @cmd = 'bcp "select ''' + @output + '''" queryout "C:\OUTPATH\data.xml"'   
    + ' -S SQLSERVERNAME -T -r -w -t -c -C RAW';   
  exec xp_cmdshell @cmd, NO_OUTPUT;  

The final step is to output the generated XML data to the file system.  Here we use the SQL Server bulk copy command BCP to write the XML data to the disk.  We can call command line programs via xp_cmdshell and the NO_OUTPUT statement suppresses success or failure messages and makes the call cleaner.

This SQL logic will then take all records in the AddAlias and output these as a single XML file.  However, the M3 Web Service doesn't like multiple API commands in a single call, so we wrap this logic in a SQL cursor and output one record per XML file.  I'll cover the cursor logic in the last post in this series.

We can then take the XML file generated and test this in SOAP UI to confirm that what we have generated is compliant with the data required for the XML call.