admin

16 January 201521 October 2018

Using XSLT to map from many-to-one

This blog is a just a lesson on how to use XSLT to append many rows into one output node using a map with a XSLT Scripting Functiod.

The source message looked something like the one below, where the Line nodes are the repeated rows that are required to be append to one string value.

<SitaBDS xmlns=”http://ECL.Online.BT.JobBooking.SITAAirNZBDS_FF”>
<BagageClaim xmlns=””>
<DS>
<Lines>
<Field>This is the first line.</Field>
</Lines>
<Lines>
<Field>This is the second line.</Field>
</Lines>
<Lines>
<Field>This is the third line.</Field>
</Lines>
</DS>
</BagageClaim>
</SitaBDS>

The final output should look like this:

<ns0:Root xmlns:ns0=”http://FFSchema.Schema1″><Field>This is the first line. This is the second line. This is the third line. </Field></ns0:Root>

Below are the steps I normally follow to work out the syntax of the XSL to be placed inside an Inline XSLT Scripting Functiod.

1. Create a BizTalk map for the source and destination schemas and add a link between the root nodes as shown below.

I initially use this this technique of linking the root elements together to obtain the correct namespaces and a template XSL code to start from.

2. Click on Validate the map to get Visual Studio to generate the XSLT mapping file. This will create the hyperlinks to the generated file as shown.

3. Clicking on the link to the output file of the XSLT will open the file in Visual Studio. Right click on the page and select View Source and take a copy of the code.

4. In Visual Studio add a new XSLT file to your project and replace the sample XSL with the generated XSL from the above step. It should look something like the code below. This method of pasting the generated XSL code provides the required namespaces and aliases to use.

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"
                exclude-result-prefixes="msxsl var s0" version="1.0"
                xmlns:ns0="http://FFSchema.Schema1"
                xmlns:s0="http://FFSchema.FlatFileSchema1">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/s0:SitaBDS" />
  </xsl:template>
  <xsl:template match="/s0:SitaBDS">
    <ns0:Root>
      <xsl:value-of select="./text()" />
    </ns0:Root>
  </xsl:template>
</xsl:stylesheet>

5. Now we need to modify the XSL between the <ns0:Root> tags to concatenate the field values from the source message. This is the point where I start to construct the XSL syntax. To append the field values the following XSL was added between the root elements.

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var s0" version="1.0"
                xmlns:ns0="http://FFSchema.Schema1"
                xmlns:s0="http://ECL.Online.BT.JobBooking.SITAAirNZBDS_FF">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/s0:SitaBDS" />
  </xsl:template>
  <xsl:template match="/s0:SitaBDS">
    <ns0:Root>

      <!-- variable to hold appended values-->
      <xsl:variable name="AppendedValue">
        <!-- iterate through each line-->
        <xsl:for-each select="BagageClaim/DS/Lines/Field">
          <xsl:value-of select="./text()" />
          <!-- add space -->
          <xsl:value-of select="' '" />
        </xsl:for-each>
      </xsl:variable>

      <!-- output the concatenated value -->
      <xsl:element name="Field">
        <xsl:value-of select="$AppendedValue"/>
      </xsl:element>
    </ns0:Root>
  </xsl:template>
</xsl:stylesheet>

6. You should be able to test the XSL syntax by setting the XML Document properties to a sample XML file. Right click anywhere on the XSLT file and select Properties on the context menu. Set the Input property to a valid sample XML source file. Once you have done that press the keys (Ctrl + Alt + F5) to start the transformation without debugging.

This should produce the following output.

<ns0:Root xmlns:ns0=”http://FFSchema.Schema1″><Field>This is the first line. This is the second line. This is the third line. </Field></ns0:Root>

7. Once we have tested the XSL code, we can now add that to our map using the Inline XSLT Functiod.

Remember when using XSL to populate a element, you are responsible for creating the destination node and all child nodes in your XSL. The final map looks like below:

Now testing the map with a real sample source message produces the following output.

Enjoy.

14 January 201521 October 2018

Concatenating XML elements in BRE without using .Net classes

Here is a solution I use to concatenate several XML elements in BRE without having to use static .Net classes. I used this method to avoid having to assert an instance of a .Net class. More in formation about about asserting .Net classes can be found here: http://msdn.microsoft.com/en-us/library/aa950269.aspx

This method simply embeds the XSLT CONCAT string function in the XPATH query. For example to concatenate 2 elements the following code would be used: concat(xpath to element 1, xpath to element 2).

In the sample message below, I am required to append the CorrelationID and JobNo values together and return the concatenated value as a vocabulary in BRE.

The XPATH statement to concatenate the two elements is shown below:

concat(/*[local-name()=’FaultEnvelope’]/*[local-name()=’CustomException’]/*[local-name()=’CorrelationID’],/*[local-name()=’FaultEnvelope’]/*[local-name()=’CustomException’]/*[local-name()=’JobNo’])

This produces the following output. However the only issue now is I require a space between the two values.

A space can be added by appending another concat statement in the xpath as shown.

Now that the XPATH statement is correctly structured, it can be added to the XPath field in Vocabulary Definition Wizard. Note the XPath selector field is required to be set to the root path “/”

In the past I have also used other XPATH functions inside the XPath field with great success.

Enjoy.

9 April 201421 October 2018

Using User Defined Table parameters of the same name in a stored procedure

When using a stored procedure that uses UDT (User defined table) you may get the following error “An item with the same key has already been added” when using the Consume Adaptor Service as shown below.

This error was due because I was using the same UDT ([NameTVP]) for 2 of parameters in the stored procedure as shown below at lines 3 and 4.

CREATE PROCEDURE dbo.bts_LoadAddress
    -- Add the parameters for the stored procedure here
    @Region [NameTVP] readonly,
    @Town [NameTVP] readonly

It appears the adaptor wizard cannot handle several User Defined Tables using the same name for the parameters. The simple resolution is to use distinct names for the UDT’s to avoid this error.

27 March 201421 October 2018

Sending JSON messages from a BizTalk2013 adaptor

I had a requirement where I needed to send a message to a web API that only accepted messages as JSON.

BizTalk2013 provides a WCF-WebHttp adaptor for sending sending XML messages to a REST endpoint but not in JSON format. There are rumours this will be remedied in BizTalk2013R2, unfortunately I required a solution now.

So in the meantime I will use the the Json.NET component to convert the XML message to JSON. I also found this blog from http://blog.quicklearn.com/2013/09/06/biztalk-server-2013-support-for-restful-services-part-45/ to convert XML messages to JSON using the Json.net in a custom pipeline component which I ended up using with some mods.

When using this pipeline remember to set the Outbound HTTP Headers as shown below:

This all worked a treat until during the unit testing phase I created a sample XML document with only one repeating element.This caused the following error from the Web API.

Can not deserialize instance of java.util.ArrayList out of VALUE_STRING token at [Source: java.io.StringReader@6b270743; line: 1, column: 152] (through reference chain: model.ExtContact["names"])

As it turned out, the Web API required all unbound elements to be sent as an array which should have looked like this “names”: [“Mahindra Morar”] but instead was being sent as “names”:”Mahindra Morar”

After reading the documentation on the Json.Net component there is an option to always force a json array for an element. This is done by adding the following attribute json:Array=”true” on the unbound element.

Here is a code snippet from the documentation that shows how it is done.

Now I though I could just simply import a schema with the element and attribute which produced the following schema output.

<ns0:Root xmlns:ns0=”http://BizTalk_Server_Project1.People”>
<ns1:Name Array=”true” xmlns:ns1=”http://james.newtonking.com/projects/json”>Tom Lee</ns1:Name>
</ns0:Root>

However the Json.net component did not like having the namespace declared inline with the unbound element when it was generated by BizTalk. The work around for this was to add the attribute to the elements in the custom pipeline component, use the XMLTranslatorStream to remove the inline namespaces or debug the source code. I ended up using the first option as I already had the XML document loaded in a XMLDoument type. To keep the solution a bit more flexible, I exposed a property on the pipeline to accept a list of comma separated xpath elements that required to be outputted as json arrays.

The Execute method in the code from http://blog.quicklearn.com/2013/09/06/biztalk-server-2013-support-for-restful-services-part-45/ was modified as shown below.

public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
        {

            #region Handle CORS Requests
            // Detect of the incoming message is an HTTP CORS request
            // http://www.w3.org/TR/cors/

            object httpMethod = null;
            httpMethod = inmsg.Context.Read(HTTP_METHOD_PROPNAME, WCF_PROPERTIES_NS);

            if (httpMethod != null && (httpMethod as string) == OPTIONS_METHOD)
            {
                // Remove the message body before returning
                var emptyOutputStream = new VirtualStream();
                inmsg.BodyPart.Data = emptyOutputStream;

                return inmsg;
            }
            #endregion


            // Make message seekable
            if (!inmsg.BodyPart.Data.CanSeek)
            {
                var originalStream = inmsg.BodyPart.Data;
                Stream seekableStream = new ReadOnlySeekableStream(originalStream);
                inmsg.BodyPart.Data = seekableStream;
                pc.ResourceTracker.AddResource(originalStream);
            }

            // Here again we are loading the entire document into memory
            // this is still a bad plan, and shouldn't be done in production
            // if you expect larger message sizes

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(inmsg.BodyPart.Data);
            
            //Get the array list of the elements required to be outputted as json arrays from the exposed pipeline property.
            string[] elementList = arrayXpathElementList.Split(',');

            //add the namespace required to force an element.
            xmlDoc.DocumentElement.SetAttribute("xmlns:json", "http://james.newtonking.com/projects/json");

            if (xmlDoc.FirstChild.LocalName == "xml")
                xmlDoc.RemoveChild(xmlDoc.FirstChild);

            
            for (int indexElement = 0; indexElement < elementList.Length; indexElement++)
            {
                XmlNodeList dataNodes = xmlDoc.SelectNodes(elementList[indexElement]);
                foreach (XmlNode node in dataNodes)
                {
                    //Add the attribute required on the element to force the creation of an json array. 
                    string ns = node.GetNamespaceOfPrefix("json");
                    XmlNode attr = xmlDoc.CreateNode(XmlNodeType.Attribute, "Array", ns);
                    attr.Value = "true";
                    node.Attributes.SetNamedItem(attr);
                }
            }
            // Remove any root-level attributes added in the process of creating the XML
            // (Think xmlns attributes that have no meaning in JSON)
            

            string jsonString =  JsonConvert.SerializeXmlNode(xmlDoc, Newtonsoft.Json.Formatting.Indented, true);

            #region Handle JSONP Request
            // Here we are detecting if there has been any value promoted to the jsonp callback property
            // which will contain the name of the function that should be passed the JSON data returned
            // by the service.

            object jsonpCallback = inmsg.Context.Read(JSONP_CALLBACK_PROPNAME, JSON_SCHEMAS_NS);
            string jsonpCallbackName = (jsonpCallback ?? (object)string.Empty) as string;

            if (!string.IsNullOrWhiteSpace(jsonpCallbackName))
                jsonString = string.Format("{0}({1});", jsonpCallbackName, jsonString);
            #endregion

            var outputStream = new VirtualStream(new MemoryStream(Encoding.UTF8.GetBytes(jsonString)));
            inmsg.BodyPart.Data = outputStream;

            return inmsg;
        }

        #endregion        
    }

After making the changes above, the component is now able to covert a unbound element with only one value to a json array correctly.

Enjoy.

17 January 201421 October 2018

Handling SQL Errors in Try-Catch blocks using the WCF SQL Adaptor

At times I am required to raise business rule type errors or T-SQL errors back to the BizTalk application from a stored procedure. This blog is about issues I encountered when using a typed stored procedure schema.

The basic structure of the stored procedure is similar to below. The SQL statements are normally wrapped inside in a Try-Catch block with any business errors raised by the RAISERROR statement.

Create PROCEDURE [dbo].[MySproc]

@RequestID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  begin try

        select @RequestID    
        
        --RAISERROR  ( 'This is my raised error',  16,1)        
        --select 1/0    
                
    
        
        return 0
    end try
    begin catch
        --error handler
        select     ERROR_NUMBER() AS ExceptionErrorNumber
                ,ERROR_MESSAGE() AS ExceptionErrorMessage;
            
        return 1    
    end catch;
END

Below is the generated typed schema using the Consume Adaptor Service wizard. The first element StoredProcedureResultSet0 is the returned dataset and second element StoredProcedureResultSet1 is the dataset representing the error structure.

When dropping a test message in, the response message looks like this below:

As expected the StoredProcedureResultSet0 contains the recordset returned. Now if I modify the stored procedure to raise an error as highlighted below.

alter PROCEDURE [dbo].[MySproc]
@RequestID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  begin try

        select @RequestID            
        RAISERROR  ( 'This is my raised error',  16,1)        
        --select 1/0    
        return 0
    end try
    begin catch
        --error handler
        select     ERROR_NUMBER() AS ExceptionErrorNumber
                ,ERROR_MESSAGE() AS ExceptionErrorMessage;
            
        return 1    
    end catch;
END

The following message will be received:

Notice the StoredProcedureRecordSet0 has the recordset value returned from the select statement and StoredProcedureRecordSet1 has the error message that was thrown from the RASIERROR statement. This is what you would expect.

Now look what happens if you cause a SQL error. I generated an error by dividing 1 by zero as highlighted below which will be caught by the Begin-Catch statement block.

alter PROCEDURE [dbo].[MySproc]
@RequestID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  begin try

        select @RequestID            
        --RAISERROR  ( 'This is my raised error',  16,1)        
        select 1/0    
        return 0
    end try
    begin catch
        --error handler
        select     ERROR_NUMBER() AS ExceptionErrorNumber
                ,ERROR_MESSAGE() AS ExceptionErrorMessage;
            
        return 1    
    end catch;
END

The response message gets suspended with the following error:

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom” with URL “mssql://.//Prototype?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.AdapterException: The ResultSet returned as part of the Typed Stored Procedure or Typed Polling invocation did not match the metadata available. If this Stored Procedure or Polling Statement can return a variable number of result sets, consider using the un-typed Stored Procedure or un-typed Polling operation instead

The reason for the suspended message is when a SQL error is raised, the error recordset is placed in another StoredProcedureRecordSet array starting at element 2 as shown below:

From this point on any further requests that generate any SQL errors will now be placed in the StoredProcedureRecordSet2 as shown until the send port is unlisted and restarted.

To solve this issue there are two options available. The first option is described below.

1. Modify the generated SQL WCF schema to include a second StoredProcedureResultSet2 as shown to capture the SQL errors.

2. Create a map with an in-line XSLT functiod to map any one of the error elements to the destination schema.

The XSLT script is shown here which uses wildcards in the path leading up to the exception errors.

<Error>
<Number><xsl:value-of select="/*/*/*/*[local-name()='ExceptionErrorNumber' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/CatchExceptions']"/></Number>
<Message><xsl:value-of select="/*/*/*/*[local-name()='ExceptionErrorMessage' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/CatchExceptions']"/></Message>
</Error>

The second option is to develop a custom pipeline component using the XML Translator Stream to rename the element names that are greater than 1 . I will continue this in part 2 of this blog.

Enjoy.

11 December 201321 October 2018

MyContents windows store app released

Have you ever tried to make a warranty or insurance claim, but you have lost the paper invoice.
This app will record the details of all your purchases and if you ever need to make a warranty or insurance claim, simply search for the item in this app and print out the purchase details.

You can download it form the windows store here:

http://apps.microsoft.com/windows/app/mycontents/c4e5ecd8-9a31-4b97-8eca-74e6506a06ab

Enjoy.

11 December 2013

Extracting large XML documents from a database

I had a scenario where I had to extract a large XML document from a stored procedure. The business requirement was to extract the data from several tables as xml that conforms to a XSD document, write it to a file, then send it via FTP.

The solution was to use SSIS and the SQL Task. This proved to be an issue because of the size of the XML extracted from the database. The process took over 20 minutes to complete and I constantly got out of memory errors.

The final solution was to use a Script task incorporating a xml reader and xml writer to solve the memory issues. The process now runs under 15 seconds.

Here is the code I wrote in the scripting task.

 public void Main()
        {
            // TODO: Add your code here
            bool fireAgain = true;
            Dts.Events.FireInformation(0, Dts.Variables["System::TaskName"].Value.ToString(), "Starting...", string.Empty, 0, ref fireAgain);
            
            ConnectionManager cm = Dts.Connections["Custs_Connection"];

            string dropFolder = (string)Dts.Connections["FILE_Xml_Extract_Folder"].ConnectionString; 
            string requestLogID = Dts.Variables["V_RequestLogID"].Value.ToString();
            string sprocName = Dts.Variables["V_ExtractStoreProcedure"].Value.ToString();    
          
            string connString = cm.ConnectionString;
            //remove the existing provider type
            connString = connString.Replace("Provider=SQLNCLI10.1;","");
            connString = connString.Replace("Auto Translate=False;", "");

            //create filename using the request log id and datetime
            string fileName = "CustomerBulkData_" + requestLogID + "_" + DateTime.Now.ToString("ddMMMyyyy_HHmmss") + ".xml";

            //close the file setting
            var settings = new XmlWriterSettings { CloseOutput = true};

            try
            {

                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sprocName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@RequestID", SqlDbType.Int).Value = requestLogID;
                    conn.Open();

                    using (XmlReader xdr = cmd.ExecuteXmlReader())
                    using (XmlWriter writer = XmlWriter.Create(File.OpenWrite(Path.Combine(dropFolder, fileName)), settings))
                    {
                        writer.WriteNode(xdr, true);
                    }

                }

                Dts.Variables["V_ExtractFileName"].Value = fileName;

                Dts.Events.FireInformation(0, Dts.Variables["System::TaskName"].Value.ToString(), "Completed.", string.Empty, 0, ref fireAgain);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, (string)Dts.Variables["System::TaskName"].Value,
                    string.Format("Error:{0}", ex),
                    null, 0);

                Dts.TaskResult = (int)DTSExecResult.Failure;
            }

        }

Enjoy.

22 November 201321 October 2018

BAM Portal missing images and layout

If you encounter this problem where the BAM Portal website is missing images and styling, it will most likely be due to the Static Content option not being enabled in the IIS role setting as shown below.

Check the Static Content checkbox highlighted above and this will resolve the issue of missing images and rendering the layout correctly.

25 September 201321 October 2018

Referenced nodes not showing in map with 2 input schemas

I had a scenario today where some source elements of type reference were not showing in a map with 2 input schemas. Creating maps with just a single input schema seemed fine.

The highlighted element below did not show the child elements for some reason.

Yet the source schema looks like this.

And when using the schema in a single input map it was fine as shown below. The child elements were shown as expected.

After some investigative work I found the issue. The schema location path in the schemas where using absolute paths as shown here: <xsd:import schemaLocation=”base_cust_3_5.xsd” …

By changing the paths to a relative path as shown in bold <xsd:import schemaLocation=”.\base_cust_3_5.xsd“… did the trick.

Enjoy.

29 August 201321 October 2018

Windows App Certification Kit – Error: Another user has installed an unpackaged version

After testing my new windows store app in debug mode, I then prepared to test my application using the Windows App Certification Kit.

After the code compiled successfully and automatically launched the certification kit, I got the following error:

This occurred because I launched the certification kit after adding my account to the local administrators group account. I could not remove the existing application as it was not listed on the desktop.

By using the following steps below I successfully removed the app and created another package to test with the certification kit.

1. Launch Windows PS (PowerShell) using the Run as administrator account

2. Execute the following command in the PS console window

Get-AppxPackage -Name *<app name>* -AllUsers

where <app name> is the name of your windows app. In my scenario the PS script looked like Get-AppxPackage –Name *mycontents* -AllUsers

3. Using the information found above, you can now determine the users who have installed the app. In my case it was the user primary\mm

4. There are two options to remove this package, either run the following PS script as the administrator or logon as the user specified in the property PackageUserInformation. In this case the user was “primary\mm”

Get-AppxPackage -Name *mycontents* –AllUsers | Remove-AppxPackage

After executing the PS script, I could now run the certification kit. Also as a side note you must be a member of the local administrators group on the computer when creating the App Package and performing the certification check.

Enjoy.

Author: admin

Using XSLT to map from many-to-one

Like this:

Concatenating XML elements in BRE without using .Net classes

Like this:

Using User Defined Table parameters of the same name in a stored procedure

Like this:

Sending JSON messages from a BizTalk2013 adaptor

Like this:

Handling SQL Errors in Try-Catch blocks using the WCF SQL Adaptor

Like this:

MyContents windows store app released

Like this:

Extracting large XML documents from a database

Like this:

BAM Portal missing images and layout

Like this:

Referenced nodes not showing in map with 2 input schemas

Like this:

Windows App Certification Kit – Error: Another user has installed an unpackaged version

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: