SoapExceptionWrapper

I wrote this code quite a while ago, but since it’s quite reusable (and very simple) here it is:

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Services.Protocols;
using System.Xml;

namespace SupportLibrary.ExceptionHandling
{

    /// <summary>
    /// Wrapper Class for Soap Exception from JBoss.
    /// Parses the XML in the Detail XML Node, exposing StatusCode and StatusDescription as properties.
    /// </summary>
    /// <example>
    /// int errorCode = -1;
    /// try
    /// {
    ///     WebService.DoSomething();
    /// }
    /// catch(SoapException soapex)
    /// {
    ///     SoapExceptionWrapper wrapper = new SoapExceptionWrapper(soapex);
    ///     int errorCode = wrapper.StatusCode;
    ///     string errorDescription = wrapper.StatusDescription;
    /// }  
    /// </example>
    public class SoapExceptionWrapper
    {
        private SoapException _ex;

        /// <summary>
        /// The XmlNode containing the Detail information about the error.
        /// </summary>
        public XmlNode DetailXml
        {
            get
            {
                return _ex.Detail.FirstChild;
            }
        }

        /// <summary>
        /// Status code from SoapException Detail XML.
        /// </summary>
        public int StatusCode
        {
            get
            {
                string codeStr = "-1";
                if(DetailXml != null && DetailXml.ChildNodes.Count > 0)
                    codeStr = DetailXml.SelectSingleNode("StatusCode").InnerText;
                int codeInt = -1;
                int.TryParse(codeStr, out codeInt);
                return codeInt;
            }
        }

        /// <summary>
        /// Status description from SoapException Detail XML.
        /// </summary>
        public string StatusDescription
        {
            get
            {
                if (DetailXml != null && DetailXml.ChildNodes.Count > 0 && DetailXml.SelectSingleNode("StatusDescription") != null && DetailXml.SelectSingleNode("StatusDescription").InnerText != "")
                {
                    return DetailXml.SelectSingleNode("StatusDescription").InnerText;
                }
                return _ex.Message;
            }
        }

        /// <summary>
        /// Constructor using default values.
        /// </summary>
        /// <param name="soapex"></param>
        public SoapExceptionWrapper(SoapException soapex)
        {
            _ex = soapex;
        }
    }
}

SQL Server 2005 XML - Msg 9402 (unable to switch the encoding)

Had a problem converting XML from a column of type text to type xml. Someone else luckily has had the same problem:
http://devio.wordpress.com/2008/03/04/retrieving-xml-data-in-sql-server-2005/.

The solution was to convert the column from type TEXT to NVARCHAR(max), and get rid of the "encoding" part of the root tag, something like this (slightly altered from the example mentioned above):

SELECT ID, CAST(
REPLACE(CAST(XmlTextColumn AS NVARCHAR(MAX)), 'encoding="UTF-16"', '')
AS XML).query('xpath to nodes') AS Node
FROM TableName

Another way to solve this problem is by adding a computed XML column:

ALTER TABLE [MyTable]
    ADD MyXMLColumn AS CAST(REPLACE(CAST(MyXMLStoredAsText AS NVARCHAR(MAX)), 'encoding="UTF-16"', '') AS XML)
    PERSISTED

If you add the persisted option, the value will be persisted with the table data, which will give better performance for read operations (slightly worse performance for insert/update operations).

Using SQL Server 2005 XML and CROSS APPLY

In my last blog entry, I used the UNPIVOT operator to get a table with products from an XML type column which were displayed as columns, to display the columns as rows. As fun as that was, it was not really a practical approach, more a way of showing how the UNPIVOT operator works.

To get a similar result, without using UNPIVOT, using in stead the values() function with the CROSS APPLY operator, we could use this query:

SELECT
ContractNumber,
col.value('Name[1]', 'nvarchar(50)') AS ProductName
FROM Contract
CROSS APPLY contractXML.nodes('Contract/Order/OrderItem') AS x(col)

This could give a table like this if there were only one contract in the table with ContractNumber = 1:

ContractNumber	ProductName
1	Product 1
1	Product 1
1	Product 2

The nodes() function returns a table "x" with one column "col".

The CROSS APPLY operator joins the result from a table-valued function with the result of an "ordinary" query. This is like a LEFT JOIN, only against a table returned by a function in stead of another table.

Using SQL Server 2005 XML And Unpivot

I have a table with contracts stored as XML. Using the XML query possibilities in SQL Server 2005 together with the UNPIVOT keyword I can get statistics on different types of contracts.

My table has these columns:
ContractNumber int (Primary Key)
contractXML xml

My XML looks something like like this, and is stored in a column of type XML:

<Contract>
  <Customer>
  ...
  </Customer>
  <Order>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product3</Name>
      <Price>13.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product2</Name>
      <Price>23.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
  </Order>
</Contract>

To get a table with a max of 5 OrderItems as columns, I can use this query:

    SELECT
      ContractNumber,
      contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)') AS Product1,
      contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)') AS Product2,
      contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)') AS Product3,
      contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)') AS Product4,
      contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)') AS Product5
    FROM Contract

 

This is nice, but what if I want to get the number of each product sold? The answer is that I can use the UNPIVOT operator!
Something like this will do it:

SELECT Name, COUNT(*) AS [Count] FROM
(
    SELECT
        ContractNumber, col, Name
    FROM
    (SELECT
    ContractNumber,
    [col1] = contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)'),
    [col2] = contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)'),
    [col3] = contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)'),
    [col4] = contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)'),
    [col5] = contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)')
    FROM Contract) col
    UNPIVOT(
        Name
        FOR col
        IN ([col1],[col2],[col3],[col4],[col5])
    ) AS unpvt
) AS T
GROUP BY PackageName

The UNPIVOT operator gives the values in the 5 columns as 1 column.

If I had only the 1 row in my Contract table from the example above, the result would be:

Name	Count
Product1	3
Product2	1
Product3	1

This is a simple example, and it has a max number of ordered products per contract of 5. Could maybe be extended.

If you want a table with the counts for the different products as columns, then something like this would do the job:

SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract

This would give this result:

Product1	Product2	Product3
3	1	1

And we could of course UNPIVOT this result too:

SELECT
   ProcuctCount
FROM
(SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract) cols
UNPIVOT(
   ProductCount
   FOR cols IN (Product1, Product2, Product3)
) AS unpvt

This should give a table like this:

ProductCount

3

1

1