How do I work with a comma-separated-string in SQL Server

Hy there!

So we have some kind of array in our domain.

eg [ 123, 234, 345 ]

When we are going to query this against SQL Server, we usually transform this array to 123,234,345 (string), so that we can do something like (with string replace):

SELECT    *
FROM      [dbo].[myTable]
WHERE     [myColumn] IN (123,234,345)

This is pretty straight forward, even开发者_如何学运维 if a WHERE [myColumn] = 123 OR ... would be better when it comes to performance.

This (the way of transforming) is the only way to get the array from the domain to the database.

Now I need, to do a join, this array to be transformed to a table. The first thing which came to my mind was that I could create a XML inside the domain and transform this somehow. Can you give me any examples how to do so or even better solutions?

I am sure that you can use comma separate string and than parse into table.

But i suggest to sent xml with ids like this:

<query id="59bd6806-d28c-451a-8473-69f1432e46b2">
  <ids>
    <id>1</id>
  </ids>
</query>

And than parse into table:

CREATE FUNCTION [dbo].[GetQueryIdsTable] 
(   
    @query xml
)
RETURNS @ids TABLE (id INT, ItemOrder INT)
AS
BEGIN

    INSERT INTO @ids (ID, ItemOrder) SELECT Ids.Id.value('.','int'), ROW_NUMBER() OVER(ORDER BY ROWCOUNT_BIG()) AS ItemOrder

    FROM @query.nodes('/query/ids/id') as Ids(Id)   

    RETURN 
END

After parsing you can join.

On the server side i use Xml builder like this:

public class Query
{
    private List<string> Ids = new List<string>();

    public void AddId(Int32 id)
    {
        if (Ids.Contains(id.ToString()))
            return;

        Ids.Add(id.ToString());
    }

    public String ToXmlString()
      {
        var idsElement = new XElement("ids");

        foreach (var id in Ids)
        {
            idsElement.Add(new XElement("id", id));
        }


        XElement query = new XElement("query",
                new XAttribute("id", Guid.NewGuid().ToString()),
            idsElement
         );

        return query.ToString();
       }

}

I like xml because you can what you want and easy parse at sql server.