Thread: XML package to determine shipping country

We're trying to setup our check out process so that if the customer is from Canada (and only from Canada) it displays a message about customs.

I was going to write an XML package to determine the shipping country and use an if statement to show it to Canada only. I'm pretty sure I've got the logic working but since I don't have access to our database I'm trying to find out where to pull the data from.

What is the name of the table that contains customer shipping addresses, and what is the column name for shipping country?

Thanks!

@donttryathome,

The "Address" table stores customer address data. The "Country" column is what you are looking for.

The following SQL query will return the shipping country for a given customer. All you need to do is insert the Customer ID.

Code:

SELECT A.Country 
FROM Address A WITH(NOLOCK)
LEFT JOIN Customer C WITH(NOLOCK) ON C.ShippingAddressID = A.AddressID
WHERE C.CustomerID = CustomerIDHere

Robert

Thanks Robert! I thought I had a pretty good grasp on how to build this package but I'm missing something.

Code:

<?xml version="1.0" encoding="utf-8" ?>
<package displayname="Canada Shipping" version="2.1" debug="true">

    <!-- ###################################################################################################### -->
    <!-- Copyright AspDotNetStorefront.com, 1995-2009.  All Rights Reserved.					                -->
    <!-- http://www.aspdotnetstorefront.com														                -->
    <!-- For details on this license please visit  the product homepage at the URL above.		                -->
    <!-- THE ABOVE NOTICE MUST REMAIN INTACT.                                                                   -->
    <!--                                                                                                        -->
    <!-- ###################################################################################################### -->

    <query name="CanCountry" rowElementName="CanCountry">

        <sql>
            <![CDATA[

			SELECT A.Country FROM Address A WITH(NOLOCK)
			LEFT JOIN Customer C WITH(NOLOCK) ON C.ShippingAddressID = A.AddressID
			WHERE C.CustomerID = @customerid

            ]]>
        </sql>

        <queryparam paramname="@customerid" paramtype="runtime" requestparamname="CustomerID" defvalue="0" sqlDataType="int" validationpattern="^\d{1,9}$"/>

    </query>

    <PackageTransform>
		<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
            <xsl:output method="html" omit-xml-declaration="yes" encoding="ISO-8859-1" />
			
            <xsl:template match="CanCountry">

				<xsl:value-of select="Country" />

			</xsl:template>

        </xsl:stylesheet>
    </PackageTransform>
</package>

This returns all of the runtime XML in a single string. What am I missing?

@donttryathome,

The entirety of the XML tree is being returned because you are missing the following element:

Code:

 
<xsl:template match="/">
</xsl:template>

There are several ways you can complete your XML package. The following XML package contains two examples.

Method A: Assign the "Country" value to a param, and later retrieve it in the "root" template element.

Method B: Create a separate template element for the "Address" node, where the child "Country" can be retrieved.

Code:

<?xml version="1.0" standalone="yes" ?>

<package version="2.1" displayname="Featured Products" debug="false">
  <query name="Shipping" rowElementName="Address" >
    <sql>
      <![CDATA[
				SELECT A.Country FROM Address A WITH(NOLOCK)
			  LEFT JOIN Customer C WITH(NOLOCK) ON C.ShippingAddressID = A.AddressID
			  WHERE C.CustomerID = @customerid
      ]]>
    </sql>
    <queryparam paramname="@customerid" paramtype="system" requestparamname="CustomerID" sqlDataType="int" defvalue="0"  validationpattern="" />
  </query>

  <PackageTransform>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf" >
      <xsl:output method="html" omit-xml-declaration="yes" />

      
      <!--Method A-->      
      <xsl:param name="ShipToCountry">
        <xsl:value-of select="/root/Shipping/Address/Country" />
      </xsl:param>
      <!--End Method A-->
      
      <xsl:template match="/" >
        
        <!--Method A-->
        Customer Shipping Country: <xsl:value-of select="$ShipToCountry" />
        <xsl:if test="$ShipToCountry = 'Canada'">
          <br/>
          This customer has a Canadian shipping address.
        </xsl:if>
        <!--End Method A-->


        <!--Method B-->
        <xsl:apply-templates select="/root/Shipping/Address"></xsl:apply-templates>
        <!--End Method B-->        
        
      </xsl:template>

      <!--Method B-->
      <xsl:template match="Address">
        <xsl:param name="ShipCountry" select="Country"></xsl:param>

        Customer Shipping Country: <xsl:value-of select="$ShipCountry" />

        <xsl:if test="$ShipCountry = 'Canada'">
          <br/>
          This customer has a Canadian shipping address.
        </xsl:if>
      </xsl:template>
      <!--End Method B-->

    </xsl:stylesheet>
  </PackageTransform>
</package>

You will also notice that the XML package contains the "if" element, which tests if "Country" is equal to "Canada" before displaying its child contents. It is important to note that this test is case sensitive. If casing is not a concern, it may be beneficial to convert casing before comparing values. This is especially valuable for user entered values.

For reference, the following example can be used to convert all the characters in a string to lower or upper case.

Code:

<xsl:variable name="smallcase" select="'abcdefghijklmnopqrstuvwxyz'" />
<xsl:variable name="uppercase" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'" />


<xsl:template match="/">
  <xsl:value-of select="translate(doc, $smallcase, $uppercase)" />
</xsl:template>

In my opinion, it is also good practice to keep the query "name" and "rowElementName" different. Mainly to avoid confusion when referencing a specific node.

Code:

 <query name="Shipping" rowElementName="Address" >

Don't hesitate to ask any questions.

Robert

Thank you so much! May I ask a possibly stupid question? You mentioned that all XML data was being returned because of the missing element:

Code:

<xsl:template match="/">
</xsl:template>

What is the difference between this template match and a named template (which I thought I should have named based on the rowElementName inside my query) such as:

Code:

<xsl:template match="Address">
</xsl:template>

I guess I'm asking, what are the appropriate scenarios for using either of these elements? What is the difference between the two?

Thanks Again! You Rock!

@donottryathome,

The difference between those templates, are the XML elements they are being associated with. In other words, the difference is the value of their "match" attribute.

match="/" associates the template with entire XML tree.

For all general purpose, you will want to have the following code in all of your XML packages:

Code:

<xsl:template match="/">
</xsl:template>

This will be your "base" template, where you can call or apply other templates if needed.

Including additional templates is not always necessary, and typically left to the developers discretion. As you can see from my sample XML package, I accomplish the same task with and without an additional template (Method A vs Method B). The decision typically comes down to performance, organization, and need. Certain advanced XSLT techniques, such as recursion and looping require additional templates.

A resource that might be worth checking out: http://www.w3schools.com/xsl/xsl_apply_templates.asp

Robert