CODEWITHSUNDEEP
sql-server
GorvGoyl
GorvGoyl

Reputation: 49260

Store list of ids in a table cell - SQL Server

I want to store list of ids (strings) in a SQL server table cell. These list of ids I'm getting while querying on another SQL server table.
These list of ids will be deserialized later into C# List <string> object.

I noticed that SQL server has some OPENJSON table-value function which can create JSON (in my case a JSON array) but I don't know how to use that for my purpose.

Update: OPENJSON is supported from SQL server 2016 but our is SQL server 2012. So OPENJSON is out of the question now.

Any other approach is also fine as long as it storing the list in single cell and can be deserialized later directly to C# List <string> object.

Note: I don't want to use a separate table for storing ids. This is just for migrating data.

Upvotes: 2

Views: 6899

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You want to store a list of IDs in one single column. From your question I take, that you are aware of the fact, that everybody, how knows a bit about RDBMSs will give you the advise to store this in a 1:n-related side table.

But you do not want this...

You have several choices:

  • Store the IDs as CSV string (3,4,17) and use some sort of string split later
  • Store the IDs as XML in an XML typed column (support goes down to SQL Server 2005) like <IDs><ID>3</ID><ID>4</ID><ID>17</ID></IDs>
  • Store the JSON you have just as the string it is into a NVARCHAR(MAX) column

(AFAIK there are no plans to introdcue a real JSON type like there is XML. JSON will be a string on storage and in representation...)

There is JSON support starting with SQL Server 2016. Are all your target system that modern?

UPDATE Create XML out of a List<string>

There are many ways, this is one of them:

var ListOfIds = new List<string>();
ListOfIds.Add("SomeID_1");
ListOfIds.Add("SomeID_2");
ListOfIds.Add("SomeID_3");

var xdoc = new XmlDocument();
var ids=xdoc.CreateNode(XmlNodeType.Element, "IDs", null);
foreach (string s in ListOfIds) {
    var id = ids.AppendChild(xdoc.CreateNode(XmlNodeType.Element, "ID", null));
    id.InnerText = s;
}
xdoc.AppendChild(ids);
var result = xdoc.InnerXml;

The result of this is a string like this

<IDs><ID>SomeID_1</ID><ID>SomeID_2</ID><ID>SomeID_3</ID></IDs>

You can pass this over to SQL Server as unicode string (C#'s default) into XML or NVARCHAR(MAX) on SQL Server side. There you can read it like this:

DECLARE @xml XML=N'<IDs><ID>SomeID_1</ID><ID>SomeID_2</ID><ID>SomeID_3</ID></IDs>';
SELECT id.value(N'(./text())[1]','nvarchar(max)') AS ID
FROM @xml.nodes(N'/IDs/ID') AS One(id)

UPDATE 2

This will create an XML in the same format as above out of table data.
If you still need help please start a new question!!

SELECT SomeID AS [*] FROM SomeWhere FOR XML PATH('ID'),ROOT('IDs'),TYPE

Upvotes: 3

Jan
Jan

Reputation: 137

You can store ids as XML using "For XML" by retrieving data, in your case probably something like: 

SELECT Id
FROM TableName
FOR XML AUTO

https://learn.microsoft.com/en-us/sql/relational-databases/xml/use-auto-mode-with-for-xml

You can retrieve your XML Data using nodes() function like this:

SELECT r.value('@id','int')
FROM TargetTable
CROSS APPLY IdsColumn.nodes('/Root/Id') AS x(r)

It should work with SQL Server 2008 +

Upvotes: 1

Related Questions