Deserializing XML in C# CLR Function.

Question

Deserializing XML in C# CLR Function.

SSC Enthusiast

Points: 175

June 11, 2019 at 3:38 pm

I'm a junior SQL guy (my senior is on vacation) and having trouble trying to figure out how to properly de-serialize XML in a CLR function written in c#. Boss is on my but to get it done. I've trimmed it down to the bare bones to demonstrate the issue. Any help would be greatly appreciated.

SQL:

CREATE FUNCTION [dbo].[udf_BUG] (@rate [float], @values [nvarchar](MAX))
RETURNS [float]
AS EXTERNAL NAME [SQLFunctions].[SQLFunctions].[udf_BUG];

-------------------------------------------------------------------------
-- Test
-------------------------------------------------------------------------

DECLARE @rate     DECIMAL(10,7) = 0.10 ,   
        @xmlData  NVARCHAR(MAX) ;  

DECLARE @tblVar AS TABLE ( [Column] DECIMAL(18,2) ) ; 
        
INSERT INTO @tblVar 
VALUES ( -10000 ) , 
       (   3000 ) , 
       (   4200 ) , 
       (   6800 ) ; 

SET @xmlData  = (SELECT * FROM @tblVar FOR XML PATH('Row') , ROOT('Table') ) ;

SELECT [BUG] = [dbo].[BUG]( @rate , @xmlData ) ;    
GO

XML:

This is the XML being passed to the CLR function

<Table><Row><Column>-10000.00</Column></Row><Row><Column>3000.00</Column></Row><Row><Column>4200.00</Column></Row><Row><Column>6800.00</Column></Row></Table>

C#

The CLR Function

using System;
using System.Collections;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Server;
 
public partial class SQLFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble udf_BUG(double rate, string xmlData)
    {
        ArrayList myArryList = new ArrayList();

        XmlTextReader myReader = new XmlTextReader(xmlData);

        // Parse the XML and pull out the data
        while (myReader.Read())
        {
            if (myReader.NodeType == XmlNodeType.Element)
            {
                if (myReader.Name == "Column")
                {
                    myArryList.Add(XmlConvert.ToDouble(myReader.ReadInnerXml()));
                }
            }
        }

        // Copies the elements of the ArrayList to a double array.
        double[] myArray = (double[])myArryList.ToArray(typeof(double));

        // < do some mor work and return result > //
        return 123.45;
    }
};

ERROR:

Msg 6522, Level 16, State 1, Line 365
A .NET Framework error occurred during execution of user-defined routine or aggregate "udf_BUG": 
System.ArgumentException: Illegal characters in path.
System.ArgumentException: 
   at System.IO.Path.CheckInvalidPathChars(String path, Boolean checkAdditional)
   at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
   at System.IO.Path.GetFullPath(String path)
   at System.Xml.XmlResolver.ResolveUri(Uri baseUri, String relativeUri)
   at System.Xml.XmlTextReaderImpl..ctor(String url, XmlNameTable nt)
   at System.Xml.XmlTextReader..ctor(String url)
   at SQLFunctions.udf_BUG(Double rate, String xmlData)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

To answer your question specifically, this is a good thread to read through. https://stackoverflow.com/questions/364253/how-to-deserialize-xml-document

That said if your main goal is to simply explode the XML and save it back into a table, I would justpass it to SQL as a string and use OPENXML to give yourself something to query.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jake5958 SSC Enthusiast Points: 175 More actions · Answer 2

Still can't get this to work.

The XML is

<Table><Row><Column>-10000.00</Column></Row><Row><Column>3000.00</Column></Row><Row><Column>4200.00</Column></Row><Row><Column>6800.00</Column></Row></Table>

I modified my C# to:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Server;
 
public partial class SQLFunctions
{

    [Serializable()]
    public class Row
    {
        [XmlElement("Column")]
        public string Column { get; set; }
    }


    [Serializable()]
    [XmlRoot("Table")]
    public class Table
    {
        [XmlArrayItem("Row", typeof(Row))]
        public Row[] Row { get; set; }
    }

    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble udf_BUG(double rate, string xmlData)
    {
        Table Rows = null;

        XmlSerializer serializer = new XmlSerializer(typeof(Table));

        StreamReader reader = new StreamReader(xmlData);
        Rows = (Table)serializer.Deserialize(reader);
        reader.Close();
        double[] myArray = new double[Rows.Row.Length];

        Int32 i = 0;

        while (i < Rows.Row.Length)
        {
            myArray = XmlConvert.ToDouble(Rows.Row.ToString());
            i++;
        }

        // < do some mor work and return result > //
        return 123.45;
    }
};

Still get the error:

Msg 6522, Level 16, State 1, Line 22

A .NET Framework error occurred during execution of user-defined routine or aggregate "udf_BUG":

System.ArgumentException: Illegal characters in path.

System.ArgumentException:

at System.IO.Path.GetFileName(String path)

at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean checkHost)

at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)

at System.IO.StreamReader..ctor(String path, Boolean detectEncodingFromByteOrderMarks)

at SQLFinancialFunctions.cudf_BUG(Double rate, String xmlData)

Jake5958 SSC Enthusiast Points: 175 More actions · Answer 3

Got it working after discussing with my senior. (thank god he call from vacation to check on me).

He explain that the results of the query are not an XML document and that I have to create a new XML document object and load the results of the query into it. The working code is below:

using System;
using System.Data.SqlTypes;
using System.Xml;
using Microsoft.SqlServer.Server;
 
public partial class SQLFunctions
{

    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble udf_BUG(double rate, string xmlData)
    {
        double number;
        int i = 0;

        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.LoadXml(xmlData.ToString());

        var nodes = xmlDoc.SelectNodes("Table/Row");
        var childNodes = xmlDoc.SelectNodes("Table/Row/Column");

        double[] myArray = new double[childNodes.Count];
      
        foreach (XmlNode childrenNode in nodes)
        {
            if (Double.TryParse(childrenNode.SelectSingleNode(".//Column").InnerText.ToString(), out number))
            {
                myArray = number;
                i++;
            }
        }

        // < do some mor work and return result > //
        return 123.45;
    }
};

Deserializing XML in C# CLR Function.

Cookies on SQLServerCentral