Passing XML to Sql Server

One may want to pass an array of data to SQL Server. The most recent reason for me was so I could look for a specific record based on multiple criteria.

There are several ways to pass XML to SQL Server. One of the simplest ways to pass the data is as a string. However other options are available such as creating an SqlXML object from a list using the following C# function from

/**
* Get an SqlXml representation of the list
*
* @param IEnumerable list: The list to convert
* @return SqlXml
**/
public static SqlXml GetSqlXml(IEnumerable list)
{
    //We don't use 'using' or dispose or close the stream,
    //since it lives in the return variable
    MemoryStream stream = new MemoryStream();
    using (XmlWriter writer = XmlWriter.Create(stream))
    {
        writer.WriteStartElement("list");
        foreach (object obj in list)
        {
            writer.WriteElementString("item", obj.ToString());
        }
        writer.WriteEndElement();
        stream.Position = 0;
        return new SqlXml(stream);
    }
}

From: http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty

Another way to create the XML representation, in VB.net, is to use the following:

Dim xmlDoc As New System.Xml.XmlDocument
Dim table As System.Xml.XmlElement = xmlDoc.CreateElement("table")
For Each child As String In New String() {"s1", "s2", "s3"}
    Dim id As System.Xml.XmlElement = xmlDoc.CreateElement("id")
    id.innerText = child
    table.AppendChild(id)
Next
xmlDoc.AppendChild(table)
 

Then pass the xmlDoc as a string. Once you have the parameter set up, you can define a stored procedure to accept an XML parameter such as the following:

CREATE PROCEDURE [dbo].[StoredProcedure1] ( @Criteria XML = NULL )
AS
BEGIN
    ...
END
 

The last important piece is to parse the XML in SQL Server. If you have the following XML:

<table>
    <id>1</id>
    <id>2</id>
    <id>3</id>
</table>
 

You can access the “id” nodes by using the following:

SELECT r.id.value('.', 'varchar(50)') AS ID
FROM @Criteria.nodes('/table/id') AS r(id)

Cosine Similarity in MS SQL

Cosine similarity measures the angle between two vectors and can be used to perform similarity between text strings. In the following code, the two input strings are vectorized and the similarity is returned as a floating point value between 0 and 1.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Adam Maus (http://adammaus.com)
-- Create date: 2012-06-20
-- Description: Cosine Similarity: http://en.wikipedia.org/wiki/Cosine_similarity
-- Determines the angle between two vectors
-- =============================================
CREATE FUNCTION [dbo].[cosine_distance]
(
@s nvarchar(4000), @t nvarchar(4000)
)
RETURNS float
AS
BEGIN
-- Create an array of letter frequencies using the unicode of @s and @t
DECLARE @sLet table(letter int, freq int) -- Pretend these are vectors
DECLARE @tLet table(letter int, freq int)
DECLARE @i int, @j int
DECLARE @c int -- The current character
-- Create the arrays
SET @i = 1
WHILE @i <= LEN(@s) BEGIN -- Use the UNICODE values for the character but not necessary SET @c = UNICODE(SUBSTRING(@s, @i, 1)) + 1 -- Determine whether this character is in the vector already SELECT @j = COUNT(letter) FROM @sLet WHERE letter = @c IF @j > 0 BEGIN
UPDATE @sLet SET freq += 1 WHERE letter = @c
END ELSE BEGIN
INSERT INTO @sLet VALUES (@c, 1)
END
-- We want to keep @tLet consistent with @sLet so insert a 0 for that letter
SELECT @j = COUNT(letter) FROM @tLet WHERE letter = @c
IF @j = 0 BEGIN
INSERT INTO @tLet VALUES (@c, 0)
END

SELECT @i += 1
END
SET @i = 1
WHILE @i <= LEN(@t) BEGIN SET @c = UNICODE(SUBSTRING(@t, @i, 1)) + 1 SELECT @j = COUNT(letter) FROM @tLet WHERE letter = @c IF @j > 0 BEGIN
UPDATE @tLet SET freq += 1 WHERE letter = @c
END ELSE BEGIN
INSERT INTO @tLet VALUES (@c, 1)
END

-- We want to keep @sLet consistent with @tLet so insert a 0 for that letter
SELECT @j = COUNT(letter) FROM @sLet WHERE letter = @c
IF @j = 0 BEGIN
INSERT INTO @sLet VALUES (@c, 0)
END

SELECT @i += 1
END
-- Compute the similarity
-- Declare the numerator for the similarity
DECLARE @numer float
SET @numer = 0
SELECT @numer += s.freq * t.freq FROM @sLet s LEFT JOIN @tLet t ON s.letter = t.letter
-- Declare the norm values and calculate the denominator for the similarity
DECLARE @sNorm int, @tNorm int
SET @sNorm = 0
SET @tNorm = 0
SELECT @sNorm += freq * freq FROM @sLet
SELECT @tNorm += freq * freq FROM @tLet
DECLARE @denom float
SET @denom = SQRT(@sNorm) * SQRT(@tNorm)
RETURN (@numer) / (@denom+1) -- The +1 eliminates the possibility 0 = @denom
END

To “install” this script using Microsoft SQL Server Management Studio, go to your database, and open Programmability > Functions and right-click on Scalar-valued Functions to add a new function.