ClrXmlShredder - a CLR Stored Procedure for shredding the Xml result of "FOR XML"

ClrXmlShredder.cs

/*

ClrXmlShredder - a CLR Stored Procedure for shredding the Xml result of "FOR XML"

Copyright Tao Klerks, June 2011, tao@klerks.biz
Licensed under the modified BSD license (license text below).

--------
Overview
--------

 SQL Server has supported conversion of tabular data into Xml since SQL Server 2000,
with the "FOR XML" clause. For turning Xml data back into tabular resultsets, it
supports "OPENXML" and since 2005 the "nodes()" Xml Function. 

 Both "OPENXML" and "nodes()" require customization / knowledge of the format of the 
Xml data to be "shredded" back into tabular format; this is in contrast to the 
"FOR XML" clause, whose "AUTO" and "RAW" options allow you to generate appropriate 
Xml automatically, without needing to know about / think about the data you are 
converting.

 Because of the "automatic" nature of this "FOR XML" generation, it's tempting to use 
it for diverse purposes in SQL Server:
 * Messaging Payload
 * Log of changed data in arbitrary updates
 * Log of exported data
 * etc

 The main problem with this approach is that it's difficult to change this 
automatically generated Xml back into tabular data. In my searches I've been unable 
to find any simple existing solutions - hence this class, I hope it helps someone!

 This proc will be maintained/enhanced if there's any interest, please check out 
http://architectshack.com/ClrXmlShredder.ashx and/or contact me with any questions.
It is being maintained as a GitHub Gist, feel free to fork and modify of course!
 
-----------
Usage Notes
-----------

ShredXml @InputXml, @AttributeElementHandling, @ConversionHandling

 * @InputXml - the Xml to be returned as a tabular resultset. The assumption is 
that this was created with the FOR XML clause. If not, it will probably still work 
correctly when the "@AttributeElementHandling" is manually set to "Elements" or 
"Attributes", as long as the Xml is "tabular" - as long as it only has 2 levels.

 * @AttributeElementHandling:
   * 0 (default): use schema to determine whether the Xml is element or atribute-based
   * 1: look for attributes
   * 2: look for elements

 * @ConversionHandling:
   * 0 (default): return all columns as NVarChar(Max), with the raw xml value.
   * 1 (requires a schema to be included in the xml): return all columns as 
      NVarChar(Max) EXCEPT binary, varbinary and image columns, which are returned 
      with their (Base64-decoded) binary value in a VarBinary(Max) column.

 * @RootElementName - specify this when you want to start collecting row (and/or
schema data) at some specific element in the provided Xml. This can be used with 
the "FOR XML" ROOT directive, or to work with any arbitrary Xml that contains nested 
tabular data. If the specified element is present multiple times in the provided Xml,
all the instances encountered will be used (and we expect to find the same column
structure!)

 * By default, the type of all output columns is NVarChar(Max). This will work 
automatically with SQL Server's automatic type conversion / type precedence rules for 
all types EXCEPT binary/varbinary/image. You have the option of specifying specific 
typed output just for these binary types (assuming "XMLSCHEMA" was specified in the 
"FOR XML" clause, and we can therefore identify the column types), by setting the 
"@ConversionHandling" parameter identified above. If you want to get a fully typed 
resultset, that could be supported, but the current code does not handle it.

 * Characters in column names that are not valid in Xml Names get auto-escaped by 
the FOR XML clause; the column names that contain these escaped characters are NOT 
automatically converted back to their original forms (but could be, ask if you're 
interested!)

 * Null handling will cause problems if you don't specify a schema AND don't specify 
the "XSINIL" option, AND have a Null value in your first row. (this is because SQL
Server then omits the element entirely, and we use the first data row to determine 
the column structure to be used). To be safe, ALWAYS make sure you specify the 
XMLSCHEMA option and/or the "XSINIL" option in your FOR XML clause!

 * If you want to access the resultset output by this CLR proc from within T-SQL, 
you encounter the same problem as with any stored proc in T-SQL, which is that there
is no equivalent to "SELECT .. INTO ..." for stored procedures. You end up having 2 
general approaches, there are examples of both in the section below:

   1) Declare a table variable or temp table with the appropriate schema, and use 
 "INSERT INTO ... EXEC dbo.ShredXml" to actually put the data into the temp table
 or table variable. This can be a pain for a large table, or if you're not sure 
 about the column datatypes, or if this is for a process that coule run on different
 schemas.

   2) Resort to "Dirty Tricks" to get SQL Server to auto-create a temp table from 
 the stored procedure output resultset. The only good approach I know of here is 
 using "OPENQUERY" or "OPENROWSET", which are options that need to be explicitly 
 enabled at the server level! 

 * Visual Studio's "Deploy" option for SQL Server projects does not support 
optional stored procedure parameters. To make the parameters optional you'll need
to deploy the proc manually, as outlined in the installation instructions below.

------------
Installation
------------

 * Compile this class, or download the DLL directly from 
http://architectshack.com/ClrXmlShredder.ashx

 * Ensure that CLR integration is enabled in sql server:
http://msdn.microsoft.com/en-us/library/ms131048.aspx
 
 * Create the assembly object in the database (using an appropriate path for the DLL!): 
CREATE ASSEMBLY ClrXmlShredder FROM 'c:\ClrXmlShredder.dll' WITH PERMISSION_SET = SAFE
 
 * Create the stored procedure in the database:
CREATE PROCEDURE [dbo].[ShredXml] (
    @InputXml [xml], 
    @AttributeElementHandling [tinyint] = 0,
    @ConversionHandling [tinyint] = 0,
    @RootElementName [nvarchar](255) = null
    )
AS EXTERNAL NAME [ClrXmlShredder].[ClrXmlShredder].[ShredXml]

 * Test: simple examples below

--------
Examples
--------

--Simplest case, using FOR XML RAW
DECLARE @XmlVariable Xml
SET @XmlVariable = (
  SELECT 1 AS FirstColumn, 'One' AS SecondColumn
  FOR XML RAW, XMLSCHEMA 
  )
SELECT @XmlVariable
EXEC ShredXml @XmlVariable
GO

--Output and restore Binary data, using FOR XML with BINARY BASE64
DECLARE @XmlVariable Xml
SET @XmlVariable = (
  SELECT 1 AS FirstColumn, 0x202020 AS SecondColumn
  FOR XML RAW, XMLSCHEMA, BINARY BASE64
  )
SELECT @XmlVariable --note the Base64 string in the Xml
EXEC ShredXml @InputXml = @XmlVariable, @ConversionHandling = 1
GO

--Use the "Root" option to FOR XML, to generate a valid xml document instead of a fragment/nodeset
DECLARE @XmlVariable Xml
SET @XmlVariable = (
  SELECT 1 AS FirstColumn, 0x202020 AS SecondColumn
  FOR XML RAW, XMLSCHEMA, BINARY BASE64, ROOT ('MyVarXml')
  )
SELECT @XmlVariable
EXEC ShredXml @InputXml = @XmlVariable, @ConversionHandling = 1, @RootElementName = 'MyVarXml'
GO

--Consume some random/arbitrary attribute-oriented Xml (as long as it's tabular and has no missing 
-- attributes in the first row!)
DECLARE @XmlVariable Xml
SET @XmlVariable = '<SomeRow FirstColumn="FirstValue" SecondColumn = "" />' 
    + '<SomeRow FirstColumn="SecondValue" SecondColumn="AnotherValue" />'
SELECT @XmlVariable
EXEC ShredXml @InputXml = @XmlVariable, @AttributeElementHandling = 1
GO

--Consume some random/arbitrary element-oriented Xml (as long as it's tabular and has no missing 
-- elements in the first row!)
DECLARE @XmlVariable Xml
SET @XmlVariable = '<SomeRow><FirstColumn>FirstValue</FirstColumn><SecondColumn  /></SomeRow>' 
    + '<SomeRow><FirstColumn>SecondValue</FirstColumn><SecondColumn>AnotherValue</SecondColumn></SomeRow>'
SELECT @XmlVariable
EXEC ShredXml @InputXml = @XmlVariable, @AttributeElementHandling = 2
GO

--Collect Rows from a specific element within some random/arbitrary Xml (as long as it's tabular and has 
-- no missing elements in the first row!)
DECLARE @XmlVariable Xml
SET @XmlVariable = '<DocumentElement>'
    + '<ArbitraryContentElement>'
    + '<SomeRow><FirstColumn>FirstValue</FirstColumn><SecondColumn  /></SomeRow>' 
    + '<SomeRow><FirstColumn>SecondValue</FirstColumn><SecondColumn>AnotherValue</SecondColumn></SomeRow>'
    + '</ArbitraryContentElement>'
    + '</DocumentElement>'
SELECT @XmlVariable
EXEC ShredXml @InputXml = @XmlVariable, @AttributeElementHandling = 2, @RootElementName = 'ArbitraryContentElement'
GO

--Collect Rows from a specific element at multiple locations within some random/arbitrary Xml (as long as it's 
-- tabular and has no missing elements in the first row!)
DECLARE @XmlVariable Xml
SET @XmlVariable = '<DocumentElement>'
    + '<SomeOtherStructure>'
    + '<ArbitraryContentElement><SomeRow FirstColumn="FirstValue" SecondColumn="" /></ArbitraryContentElement>' 
    + '</SomeOtherStructure>'
    + '<SomeOtherStructure>'
    + '<ArbitraryContentElement><SomeRow FirstColumn="SecondValue" SecondColumn="AnotherValue" /></ArbitraryContentElement>'
    + '</SomeOtherStructure>'
    + '</DocumentElement>'
SELECT @XmlVariable
EXEC ShredXml @InputXml = @XmlVariable, @AttributeElementHandling = 1, @RootElementName = 'ArbitraryContentElement'
GO


--INSERT INTO example
DECLARE @TestTable TABLE (OneColumn Int, AnotherColumn NVarChar(10))
DECLARE @XmlVariable Xml
SET @XmlVariable = (
  SELECT 1 AS FirstColumn, 'One' AS SecondColumn
  FOR XML RAW, XMLSCHEMA 
  )
INSERT INTO @TestTable
EXEC ShredXml @XmlVariable, 0, 0
SELECT * FROM @TestTable
GO

------------
--OPENROWSET example for getting an automatically-generated temp table with the contents of the Xml:
------------
--sp_configure 'show advanced options', 1
--reconfigure
--GO
--sp_configure 'Ad Hoc Distributed Queries', 1
--reconfigure
--GO
--sp_configure 'show advanced options', 0
--reconfigure
--GO
SELECT  * 
INTO #TempResultSet
FROM OPENROWSET (
	'SQLOLEDB',
	'Server=(local)\SQLEXPRESS;TRUSTED_CONNECTION=YES;',
	'SET FMTONLY OFF  
DECLARE @XmlVariable Xml
SET @XmlVariable = (
  SELECT 1 AS FirstColumn, ''One'' AS SecondColumn
  FOR XML RAW, XMLSCHEMA 
  )
EXEC MyDBName.dbo.ShredXml @XmlVariable, 0, 0
')
SELECT * FROM #TempResultSet
DROP TABLE #TempResultSet


=======
License
=======

Redistribution and use in source and binary forms, with or without modification, are 
permitted provided that the following conditions are met:

 - Redistributions of source code must retain the above copyright notice, this list of 
conditions and the following disclaimer.
 - Redistributions in binary form must reproduce the above copyright notice, this list 
of conditions and the following disclaimer in the documentation and/or other materials
provided with the distribution.
 - The name of the author may not be used to endorse or promote products derived from 
this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, 
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY 
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 
PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, 
WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY 
OF SUCH DAMAGE.

*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Reflection;
using System.Runtime.InteropServices;

[assembly: AssemblyTitle("ClrXmlShredder")]
[assembly: AssemblyDescription("A CLR Stored Procedure for shredding the Xml result \"FOR XML\" queries")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("ClrXmlShredder")]
[assembly: AssemblyCopyright("Copyright © Tao Klerks 2011")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: ComVisible(false)]
[assembly: AssemblyVersion("1.0.2.*")]

public class ClrXmlShredder
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ShredXml(SqlXml InputXml,
        [SqlFacet(IsNullable = true), Optional]SqlByte AttributeElementHandling,
        [SqlFacet(IsNullable = true), Optional]SqlByte ConversionHandling, 
        [SqlFacet(MaxSize = 255, IsNullable = true), Optional, DefaultParameterValue(null)]string RootElementName
        )
    {
        //Assume the "AttributeElementHandling" value provided was valid (TODO: error-handling here)
        AttributeElementHandlingPreference attributeElementHandling = AttributeElementHandlingPreference.DetectFromSchema;
        if (!AttributeElementHandling.IsNull)
            attributeElementHandling = (AttributeElementHandlingPreference)AttributeElementHandling.Value;

        //Assume the "ConversionHandling" value provided was valid (TODO: error-handling here)
        TypeConversionHandlingPreference conversionHandling = TypeConversionHandlingPreference.NoConversion;
        if (!ConversionHandling.IsNull)
            conversionHandling = (TypeConversionHandlingPreference)ConversionHandling.Value;

        if (conversionHandling == TypeConversionHandlingPreference.AllConversion)
            throw new Exception("Sorry, the \"All Conversion\" option has not been implemented yet! " +
                "Are you sure you need it? (Standard SQL Server type precedence will auto-convert " +
                "the NVarChar(Max) data to the original corresponding types without issue!)");

        SqlPipe pipe = SqlContext.Pipe;
        using (XmlReader inputReader = InputXml.CreateReader())
        {
            bool requestedRootFound = string.IsNullOrEmpty(RootElementName);
            bool firstElementFound = false;
            List<string> firstRowValues = null;
            SqlMetaData[] outputColumns = null;
            SqlDataRecord outputRecord = null;
            string rowElementName = null;

            while (inputReader.Read())
            {
                if (!requestedRootFound && inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals(RootElementName))
                {
                    requestedRootFound = true;
                    continue;
                }

                if (requestedRootFound && inputReader.NodeType == XmlNodeType.EndElement && inputReader.Name.Equals(RootElementName))
                {
                    requestedRootFound = false;
                    continue;
                }

                if (requestedRootFound)
                {
                    if (inputReader.NodeType == XmlNodeType.Element && !firstElementFound)
                    {
                        if (inputReader.Name.Equals("xsd:schema"))
                            outputColumns = BuildColumnsFromSchema(inputReader, ref rowElementName, conversionHandling, ref attributeElementHandling);
                        else
                        {
                            if (attributeElementHandling == AttributeElementHandlingPreference.DetectFromSchema)
                                throw new Exception("Attribute/Element handling preference was set to \"Detect from Schema\", but " +
                                    "no Schema is present in the Xml. Please specify 1 for Attribute-centric Xml or 2 for " +
                                    "Element-centric Xml.");

                            if (conversionHandling == TypeConversionHandlingPreference.BinaryConversionOnly
                                || conversionHandling == TypeConversionHandlingPreference.AllConversion)
                                throw new Exception("Conversion handling preference must be set to 0 (\"no conversion\") because " +
                                    "there is no Schema in the provided Xml.");

                            if (attributeElementHandling == AttributeElementHandlingPreference.Elements)
                                outputColumns = BuildColumnsFromFirstRowElements(inputReader, ref firstRowValues, ref rowElementName);
                            else if (attributeElementHandling == AttributeElementHandlingPreference.Attributes)
                                outputColumns = BuildColumnsFromFirstRowAttributes(inputReader, ref firstRowValues, ref rowElementName);
                            else
                                throw new Exception("invalid option specified for Attribute/Element handling preference.");
                        }

                        outputRecord = new SqlDataRecord(outputColumns);
                        pipe.SendResultsStart(outputRecord);

                        if (firstRowValues != null)
                        {
                            for (int i = 0; i < firstRowValues.Count; i++)
                            {
                                SetRecordValueFromString(outputRecord, outputColumns, i, firstRowValues[i]);
                            }
                            pipe.SendResultsRow(outputRecord);
                        }

                        firstElementFound = true;
                    }
                    else if (inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals(rowElementName))
                    {
                        if (attributeElementHandling == AttributeElementHandlingPreference.Elements)
                            FillRecordFromElements(inputReader, outputRecord, outputColumns, rowElementName);
                        else if (attributeElementHandling == AttributeElementHandlingPreference.Attributes)
                            FillRecordFromAttributes(inputReader, outputRecord, outputColumns);

                        pipe.SendResultsRow(outputRecord);
                    }
                }
            }

            if (pipe.IsSendingResults)
                pipe.SendResultsEnd();
        }
    }

    private static SqlMetaData[] BuildColumnsFromSchema(XmlReader inputReader, ref string rowElementName,
        TypeConversionHandlingPreference conversionHandling, ref AttributeElementHandlingPreference attributeElementHandling)
    {
        List<SqlMetaData> tempMetaData = new List<SqlMetaData>();
        bool endFound = false;
        bool columnsRegionFound = false;
        while (!endFound && inputReader.Read())
        {
            if (inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals("xsd:sequence"))
            {
                columnsRegionFound = true;
                if (attributeElementHandling == AttributeElementHandlingPreference.Attributes)
                    throw new Exception("Attribute/Element handling set to \"Attribute\", but the schema in the provided Xml specifies " +
                        "an element-based layout!");
                attributeElementHandling = AttributeElementHandlingPreference.Elements;
            }
            else if (!columnsRegionFound && inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals("xsd:element"))
            {
                inputReader.MoveToAttribute("name");
                rowElementName = inputReader.ReadContentAsString();
            }
            else if (columnsRegionFound && inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals("xsd:element"))
            {
                tempMetaData.Add(GetSqlColumnMetadataFromSchemaEntry(inputReader, conversionHandling));
            }
            else if (inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals("xsd:attribute"))
            {
                if (attributeElementHandling == AttributeElementHandlingPreference.DetectFromSchema)
                    attributeElementHandling = AttributeElementHandlingPreference.Attributes;
                else if (attributeElementHandling == AttributeElementHandlingPreference.Elements)
                    throw new Exception("Attribute/Element handling set to \"Elements\", but the schema in the provided Xml specifies " +
                        "an attribute-based layout!");

                if (!columnsRegionFound)
                    columnsRegionFound = true;

                tempMetaData.Add(GetSqlColumnMetadataFromSchemaEntry(inputReader, conversionHandling));
            }
            else if (inputReader.NodeType == XmlNodeType.EndElement && inputReader.Name.Equals("xsd:sequence"))
            {
                columnsRegionFound = false;
            }
            else if (inputReader.NodeType == XmlNodeType.EndElement && inputReader.Name.Equals("xsd:schema"))
            {
                endFound = true;
            }
        }
        return tempMetaData.ToArray();
    }

    private static SqlMetaData GetSqlColumnMetadataFromSchemaEntry(XmlReader inputReader,
        TypeConversionHandlingPreference conversionHandling)
    {
        inputReader.MoveToAttribute("name");
        string columnName = inputReader.ReadContentAsString();
        SqlDbType type = SqlDbType.NVarChar; //default to NVarChar(Max)

        if (conversionHandling == TypeConversionHandlingPreference.BinaryConversionOnly)
        {
            if (inputReader.MoveToAttribute("type"))
            {
                string sqlType = inputReader.ReadContentAsString();
                if (sqlType.Equals("sqltypes:image"))
                    type = SqlDbType.VarBinary;
            }
            else
            {
                //we know there will be a "xsd:simpleType/xsd:restriction", (because there was no 
                // "type" attribute), so pick up the type from there.
                if (inputReader.ReadToFollowing("xsd:simpleType")
                    && inputReader.ReadToFollowing("xsd:restriction")
                    )
                {
                    string sqlBaseType = inputReader.GetAttribute("base");
                    if (sqlBaseType.Equals("sqltypes:varbinary")
                        || sqlBaseType.Equals("sqltypes:binary")
                        )
                        type = SqlDbType.VarBinary;
                }
            }
        }

        return new SqlMetaData(columnName, type, -1);
    }

    private static SqlMetaData[] BuildColumnsFromFirstRowElements(XmlReader inputReader, ref List<string> firstRowValues,
        ref string rowElementName)
    {
        List<SqlMetaData> tempMetaData = new List<SqlMetaData>();
        firstRowValues = new List<string>();
        bool continueSearching = true;
        bool skipRead = false;
        rowElementName = inputReader.Name;

        while (continueSearching && (skipRead || inputReader.Read()))
        {
            skipRead = false;
            if (inputReader.NodeType == XmlNodeType.Element)
            {
                bool valueIsNull = false;
                tempMetaData.Add(new SqlMetaData(inputReader.Name, SqlDbType.NVarChar, -1));
                if (inputReader.HasAttributes
                    && inputReader.MoveToAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance")
                    && inputReader.ReadContentAsString().Equals("true")
                    )
                    valueIsNull = true;

                if (valueIsNull)
                    firstRowValues.Add(null);
                else
                {
                    firstRowValues.Add(inputReader.ReadElementContentAsString());
                    skipRead = true;
                }
            }
            else if (inputReader.NodeType == XmlNodeType.EndElement && inputReader.Name.Equals(rowElementName))
            {
                continueSearching = false;
            }
        }
        return tempMetaData.ToArray();
    }

    private static SqlMetaData[] BuildColumnsFromFirstRowAttributes(XmlReader inputReader, ref List<string> firstRowValues,
        ref string rowElementName)
    {
        List<SqlMetaData> tempMetaData = new List<SqlMetaData>();
        firstRowValues = new List<string>();
        rowElementName = inputReader.Name;

        int attributeCount = inputReader.AttributeCount;
        int currentAttributeID = 0;

        while (currentAttributeID < attributeCount)
        {
            inputReader.MoveToAttribute(currentAttributeID);
            if (!inputReader.Name.Equals("xmlns"))
            {
                tempMetaData.Add(new SqlMetaData(inputReader.Name, SqlDbType.NVarChar, -1));
                firstRowValues.Add(inputReader.ReadContentAsString());
            }
            currentAttributeID++;
        }
        return tempMetaData.ToArray();
    }

    private static void FillRecordFromElements(XmlReader inputReader, SqlDataRecord outputRecord, SqlMetaData[] outputColumns,
        string rowElementName)
    {
        bool continueReading = true;
        bool skipRead = false;
        int expectedColumnID = 0;

        while (continueReading && (skipRead || inputReader.Read()))
        {
            skipRead = false;
            if (inputReader.NodeType == XmlNodeType.Element)
            {
                //collect nulls for any missing columns - if "XSINIL" was not set when calling 
                while (!inputReader.Name.Equals(outputColumns[expectedColumnID].Name))
                {
                    if (expectedColumnID == outputColumns.Length - 1)
                    {
                        //we ran out of columns - thrown an error
                        throw new Exception("Unknown element found! (is it possible you did not specify a schema, did not specify " +
                            "the \"XSINIL\" option, and had a null value in the first row? This would prevent successful column " +
                            "auto-detection...");
                    }
                    outputRecord.SetValue(expectedColumnID, null);
                    expectedColumnID++;
                }


                bool valueIsNull = false;
                if (inputReader.HasAttributes
                    && inputReader.MoveToAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance")
                    && inputReader.ReadContentAsString().Equals("true")
                    )
                    valueIsNull = true;

                if (valueIsNull)
                {
                    outputRecord.SetValue(expectedColumnID, null);
                }
                else
                {
                    SetRecordValueFromString(outputRecord, outputColumns, expectedColumnID, inputReader.ReadElementContentAsString());
                    skipRead = true;
                }
                expectedColumnID++;
            }
            else if (inputReader.NodeType == XmlNodeType.EndElement && inputReader.Name.Equals(rowElementName))
            {
                continueReading = false;
            }
        }

        //set any missing columns at the end to null
        while (expectedColumnID < outputColumns.Length)
        {
            outputRecord.SetValue(expectedColumnID, null);
            expectedColumnID++;
        }
    }

    private static void FillRecordFromAttributes(XmlReader inputReader, SqlDataRecord outputRecord, SqlMetaData[] outputColumns)
    {
        int expectedColumnID = 0;
        int attributeCount = inputReader.AttributeCount;
        int currentAttributeID = 0;

        while (currentAttributeID < attributeCount)
        {
            inputReader.MoveToAttribute(currentAttributeID);
            if (!inputReader.Name.Equals("xmlns"))
            {
                //collect nulls for any missing columns
                while (!inputReader.Name.Equals(outputColumns[expectedColumnID].Name))
                {
                    if (expectedColumnID == outputColumns.Length - 1)
                    {
                        //we ran out of columns - thrown an error
                        throw new Exception("Unknown attribute found! (is it possible you did not specify a schema, and had a null " +
                            "value in the first row? This would prevent successful column auto-detection...)");
                    }
                    outputRecord.SetValue(expectedColumnID, null);
                    expectedColumnID++;
                }

                SetRecordValueFromString(outputRecord, outputColumns, expectedColumnID, inputReader.ReadContentAsString());
                expectedColumnID++;
            }
            currentAttributeID++;
        }

        //set any missing columns at the end to null
        while (expectedColumnID < outputColumns.Length)
        {
            outputRecord.SetValue(expectedColumnID, null);
            expectedColumnID++;
        }
    }

    private static void SetRecordValueFromString(SqlDataRecord outputRecord, SqlMetaData[] columns, int columnID, string valueString)
    {
        if (valueString == null)
            outputRecord.SetValue(columnID, null);
        else
        {
            if (columns[columnID].DbType == DbType.Binary)
            {
                byte[] binaryData = Convert.FromBase64String(valueString);
                outputRecord.SetBytes(columnID, 0, binaryData, 0, binaryData.Length);
            }
            else
            {
                outputRecord.SetSqlString(columnID, new SqlString(valueString));
            }
        }
    }

    public enum AttributeElementHandlingPreference : byte
    {
        DetectFromSchema = 0,
        Attributes = 1,
        Elements = 2
    }

    public enum TypeConversionHandlingPreference : byte
    {
        NoConversion = 0,
        BinaryConversionOnly = 1,
        AllConversion = 2
    }
}