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));
}
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();
}
}
}
