XML as Stored Procedure parameter in SQL Server
Did you ever tried to pass arrays into stored procedures or may be complex types or just tried to convert types and join lines?
SQL Server starting from 2005 has support for XML which will allow you to make things happen.
Here is few examples.
IF OBJECT_ID('spXmlSampleSimple','P') IS NOT NULL DROP PROCEDURE spXmlSampleSimple
GO
CREATE PROCEDURE spXmlSampleSimple(@Order XML)
AS
-- Retrive single values
DECLARE @OrderID INT;
DECLARE @ManagerID INT;
SET @OrderID = @Order.value('/Order[1]/OrderID[1]', 'INT');
SET @ManagerID = @Order.value('/Order[1]/Manager[1]/ManagerID[1]', 'INT');
SELECT @OrderID AS OrderID, @ManagerID AS ManagerID;
-- Arrays
SELECT Phones.Phone.value('.','NVARCHAR(20)') AS CustomerPhones FROM @Order.nodes('//Phone') as Phones(Phone);
-- Arrays into table
DECLARE @Phones TABLE (Phones NVARCHAR(20))
INSERT INTO @Phones(Phones) SELECT Phones.Phone.value('.','NVARCHAR(20)') FROM @Order.nodes('//Phone') as Phones(Phone);
SELECT * FROM @Phones;
-- Aggregations
DECLARE @ItemsCount INT;
SET @ItemsCount = @Order.value('count(/Order/Items/Item)', 'INT');
SELECT @ItemsCount AS ItemsCount;
GO
EXEC spXmlSampleSimple @Order = '
<Order>
<OrderID>123</OrderID>
<Manager>
<ManagerID>1</ManagerID>
<Email>[email protected]</Email>
</Manager>
<Customer>
<Phones>
<Phone>0919315555</Phone>
<Phone>0445340940</Phone>
</Phones>
</Customer>
<Items>
<Item>
<ItemID>2</ItemID>
<Quantity>1</Quantity>
</Item>
<Item>
<ItemID>3</ItemID>
<Quantity>2</Quantity>
</Item>
</Items>
</Order>
'This is simple domanstration of how can you pass xml serialized C# object right into stored procedure, and what is most important from now on you may not sync parameters and convert types by hand.
And here is even more complex example demonstrating xml schema which will validate input xml before doing anything
IF OBJECT_ID('spXmlSampleComplex','P') IS NOT NULL DROP PROCEDURE spXmlSampleComplex
GO
IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'OrderSchema') DROP XML SCHEMA COLLECTION OrderSchema
GO
CREATE XML SCHEMA COLLECTION OrderSchema AS '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderID" minOccurs="1" maxOccurs="1" type="xs:positiveInteger" />
<xs:element name="Manager" minOccurs="1" maxOccurs="1">
<xs:complexType>
<xs:all>
<xs:element name="ManagerID" minOccurs="1" maxOccurs="1" type="xs:positiveInteger" />
<xs:element name="Email" type="EmailAddress" minOccurs="1" maxOccurs="1"/>
</xs:all>
</xs:complexType>
</xs:element>
<xs:element name="Customer" minOccurs="1" maxOccurs="1">
<xs:complexType>
<xs:all>
<xs:element name="Phones" minOccurs="1" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Phone" type="PhoneString" minOccurs="1" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:all>
</xs:complexType>
</xs:element>
<xs:element name="Items" minOccurs="1" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Item" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:all>
<xs:element name="ItemID" minOccurs="1" maxOccurs="1" type="xs:positiveInteger" />
<xs:element name="Quantity" minOccurs="1" maxOccurs="1" type="xs:positiveInteger" />
</xs:all>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:simpleType name="positiveDecimal">
<xs:restriction base="xs:decimal">
<xs:minExclusive value="0"/>
<xs:fractionDigits value="2"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="NonEmptyString">
<!-- we can describe our types separately to reuse them later -->
<xs:restriction base="xs:string">
<xs:minLength value="1" />
<xs:pattern value=".*[^\s].*" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="EmailAddress">
<xs:restriction base="xs:string">
<xs:pattern value="[^@]+@[^\.]+\..+"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="PhoneString">
<xs:restriction base="xs:string">
<xs:pattern value="[0-9]{10}"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>'
GO
DECLARE @xml XML(OrderSchema)
SELECT @xml = '<?xml version="1.0" encoding="utf-8"?>
<Order>
<OrderID>123</OrderID>
<Manager>
<ManagerID>1</ManagerID>
<Email>[email protected]</Email>
</Manager>
<Customer>
<Phones>
<Phone>0919315555</Phone>
<Phone>0445340940</Phone>
</Phones>
</Customer>
<Items>
<Item>
<ItemID>2</ItemID>
<Quantity>1</Quantity>
</Item>
<Item>
<ItemID>3</ItemID>
<Quantity>2</Quantity>
</Item>
</Items>
</Order>
'
SELECT @xml.value('/Order[1]/Items[1]/Item[1]/ItemID[1]', 'INT');
GO
CREATE PROCEDURE spXmlSampleComplex(@Order XML(OrderSchema))
AS
-- Retrive single values
DECLARE @OrderID INT;
DECLARE @ManagerID INT;
SET @OrderID = @Order.value('/Order[1]/OrderID[1]', 'INT');
SET @ManagerID = @Order.value('/Order[1]/Manager[1]/ManagerID[1]', 'INT');
SELECT @OrderID AS OrderID, @ManagerID AS ManagerID;
-- Arrays
SELECT Phones.Phone.value('.','NVARCHAR(20)') AS CustomerPhones FROM @Order.nodes('//Phone') as Phones(Phone);
-- Arrays into table
DECLARE @Phones TABLE (Phones NVARCHAR(20))
INSERT INTO @Phones(Phones) SELECT Phones.Phone.value('.','NVARCHAR(20)') FROM @Order.nodes('//Phone') as Phones(Phone);
SELECT * FROM @Phones;
-- Aggregations
DECLARE @ItemsCount INT;
SET @ItemsCount = @Order.value('count(/Order/Items/Item)', 'INT');
SELECT @ItemsCount AS ItemsCount;
GO
EXEC spXmlSampleComplex @Order = '
<Order>
<OrderID>123</OrderID>
<Manager>
<ManagerID>1</ManagerID>
<Email>[email protected]</Email>
</Manager>
<Customer>
<Phones>
<Phone>0919315555</Phone>
<Phone>0445340940</Phone>
</Phones>
</Customer>
<Items>
<Item>
<ItemID>2</ItemID>
<Quantity>1</Quantity>
</Item>
<Item>
<ItemID>3</ItemID>
<Quantity>2</Quantity>
</Item>
</Items>
</Order>
'From a C# perspective code will look something like this:
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using System.Xml.Serialization;
namespace XmlSample
{
public class Order
{
public int OrderID { get; set; }
public Manager Manager { get; set; }
public Customer Customer { get; set; }
public List<Item> Items { get; set; }
}
public class Manager
{
public int ManagerID { get; set; }
public string Email { get; set; }
}
public class Customer
{
[XmlArrayItem("Phone")]
public List<string> Phones { get; set; }
}
public class Item
{
public int ItemID { get; set; }
public int Quantity { get; set; }
}
public class Program
{
private static void Main(string[] args)
{
var order = new Order
{
OrderID = 123,
Manager = new Manager
{
ManagerID = 1,
Email = "[email protected]"
},
Customer = new Customer
{
Phones = new List<string> {
"0919315555",
"0445340940"
}
},
Items = new List<Item> {
new Item
{
ItemID = 2,
Quantity = 1
},
new Item
{
ItemID = 3,
Quantity = 2
}
}
};
Console.WriteLine("INPUT");
Console.WriteLine("-------------------------------------");
Console.WriteLine(Serialize(order));
Console.WriteLine(Environment.NewLine);
Console.WriteLine("OUTPUT");
Console.WriteLine("-------------------------------------");
var dataSet = spXmlSampleComplex(order);
Console.WriteLine(JsonConvert.SerializeObject(dataSet, Newtonsoft.Json.Formatting.Indented));
}
private static string Serialize(Order order)
{
var xmlSerializer = new XmlSerializer(typeof(Order));
var stringBuilder = new StringBuilder();
using (var xmlWriter = XmlWriter.Create(stringBuilder, new XmlWriterSettings { Indent = true }))
{
xmlSerializer.Serialize(xmlWriter, order);
}
return stringBuilder.ToString();
}
private static DataSet spXmlSampleComplex(Order order)
{
DataSet dataSet = new DataSet();
using (var connection = new SqlConnection((new SqlConnectionStringBuilder { InitialCatalog = "Play", IntegratedSecurity = true }).ConnectionString))
{
using (var command = new SqlCommand("spXmlSampleComplex", connection) { CommandType = CommandType.StoredProcedure })
{
command.Parameters.Add(new SqlParameter("@Order", Serialize(order)));
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);
}
}
return dataSet;
}
}
}Which will output:
INPUT
-------------------------------------
<?xml version="1.0" encoding="utf-16"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<OrderID>123</OrderID>
<Manager>
<ManagerID>1</ManagerID>
<Email>[email protected]</Email>
</Manager>
<Customer>
<Phones>
<Phone>0919315555</Phone>
<Phone>0445340940</Phone>
</Phones>
</Customer>
<Items>
<Item>
<ItemID>2</ItemID>
<Quantity>1</Quantity>
</Item>
<Item>
<ItemID>3</ItemID>
<Quantity>2</Quantity>
</Item>
</Items>
</Order>
OUTPUT
-------------------------------------
{
"Table": [
{
"OrderID": 123,
"ManagerID": 1
}
],
"Table1": [
{
"CustomerPhones": "0919315555"
},
{
"CustomerPhones": "0445340940"
}
],
"Table2": [
{
"Phones": "0919315555"
},
{
"Phones": "0445340940"
}
],
"Table3": [
{
"ItemsCount": 2
}
]
}Just checked this froks from SQL 2005 up to SQL 2016.
You can even try to implement some crazy stuff like this:
public static class StoredProcedure<T>
{
private static string Serialize(T input)
{
var xmlSerializer = new XmlSerializer(typeof(T));
var stringBuilder = new StringBuilder();
using (var xmlWriter = XmlWriter.Create(stringBuilder, new XmlWriterSettings { Indent = true }))
{
xmlSerializer.Serialize(xmlWriter, input);
}
return stringBuilder.ToString();
}
public static DataSet Exec(T input, [CallerMemberName] string storedProcedure = null, string storedProcedurePrefix = "sp")
{
DataSet dataSet = new DataSet();
using (var connection = new SqlConnection((new SqlConnectionStringBuilder { InitialCatalog = "Play", IntegratedSecurity = true }).ConnectionString))
{
using (var command = new SqlCommand(storedProcedurePrefix + storedProcedure, connection) { CommandType = CommandType.StoredProcedure })
{
command.Parameters.Add(new SqlParameter("@" + typeof(T).Name, Serialize(input)));
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);
}
}
return dataSet;
}
}
public static class StoredProcedure
{
public static DataSet XmlSampleSimple(Order order)
{
return StoredProcedure<Order>.Exec(order);
}
public static DataSet XmlSampleComplex(Order order)
{
return StoredProcedure<Order>.Exec(order);
}
}And now your call will be as simple as:
var dataSet1 = StoredProcedure.XmlSampleSimple(order);
var dataSet2 = StoredProcedure.XmlSampleComplex(order);And if your stored procedure will retrieve XML also magic can happen :)