Skip to content

Instantly share code, notes, and snippets.

@TaoK

TaoK/ClrXmlShredder.cs

Created June 19, 2011 21:16
Show Gist options
  • Save TaoK/1034768 to your computer and use it in GitHub Desktop.
Save TaoK/1034768 to your computer and use it in GitHub Desktop.
Download ZIP
ClrXmlShredder - a CLR Stored Procedure for shredding the Xml result of "FOR XML"
Raw
ClrXmlShredder.cs
/*
ClrXmlShredder - a CLR Stored Procedure for shredding the Xml result of "FOR XML"
Copyright Tao Klerks, June 2011, [email protected]
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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment