A SQL CLR Stored Procedure for converting XML created by a FOR XML clause, back into a single tabular result set, with one stored procedure call without knowledge of the original table/query schema.
/*
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).
http://architectshack.com/ClrXmlShredder.ashx
https://gist.github.com/TaoK/1034768
--------
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
}
}