{"id":162,"date":"2014-04-10T22:28:23","date_gmt":"2014-04-10T21:28:23","guid":{"rendered":"http:\/\/thedifficultbit.com\/?p=162"},"modified":"2018-12-24T22:34:14","modified_gmt":"2018-12-24T21:34:14","slug":"using-xml-to-pass-lists-as-parameters-in-sql-server-the-c-side","status":"publish","type":"post","link":"https:\/\/thedifficultbit.com\/?p=162","title":{"rendered":"Using XML to pass lists as parameters in SQL Server: the C# Side"},"content":{"rendered":"\n<p>The blog post &#8220;<a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/using-xml-to-pass-lists-as-parameters-in-sql-server\/\">Using XML to pass lists as parameters in SQL Server<\/a>&#8221; by the wonderfully-named <a href=\"https:\/\/www.red-gate.com\/simple-talk\/author\/phil-factor\/\">Phil Factor<\/a> outlines a useful technique to pass lists into stored procedures and functions. Here is a simple function to create the appropriate XML in C# code (assumes it is a list of integers):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\/\/\/ &lt;summary>\n\/\/\/ Formats a list of integers for use as a parameter to a sql function.\n\/\/\/ &lt;\/summary>\n\/\/\/ &lt;remarks>\n\/\/\/ SQL:\n\/\/\/ &lt;code>SELECT x.y.value('.','int') AS IDs FROM @XMLList.nodes('\/list\/i\/text()') AS x(y)&lt;\/code>\n\/\/\/ Courtesy of https:\/\/www.simple-talk.com\/blogs\/using-xml-to-pass-lists-as-parameters-in-sql-server\/.\n\/\/\/ &lt;\/remarks>\npublic static XElement ListIntegersAsXElementsForSqlParameter(IEnumerable&lt;int> values)\n{\n\tif (!values.Any())\n\t{\n\t\treturn null;\n\t}\n\n\tXElement node = new XElement(\"list\");\n\tforeach (int value in values)\n\t{\n\t\tnode.Add(new XElement(\"i\", value));\n\t}\n\n\treturn node;\n}\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The blog post &#8220;Using XML to pass lists as parameters in SQL Server&#8221; by the wonderfully-named Phil Factor outlines a useful technique to pass lists into stored procedures and functions. Here is a simple function to create the appropriate XML in C# code (assumes it is a list of integers):<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-162","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=\/wp\/v2\/posts\/162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=162"}],"version-history":[{"count":1,"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=\/wp\/v2\/posts\/162\/revisions"}],"predecessor-version":[{"id":163,"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=\/wp\/v2\/posts\/162\/revisions\/163"}],"wp:attachment":[{"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thedifficultbit.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}