Working with XML in C# and SQL Server

Đôi khi một lập trình viên có thể nhận một task nhất định liên quan đến việc thao tác dữ liệu XML trên SQL Server, hoặc đơn giản cần insert một list data từ C# xuống data SQL. Khi đó chúng ta có thể chuyển list data thành một XML rồi thao tác dễ dàng. Bài viết này sẽ hữu ích trong những tình huống như vậy.

Thực hiện convert một object, list object trong C# thành XML

Chúng ta có một đối tượng là Student cần chuyển đổi.

public class Student     
{        
        public int Id { get; set; }         
        public string Name { get; set; }   
        public int Age { get; set; }           public string Phone { get; set; }           public string Email { get; set; }           public double? Score { get; set; }   
}

Danh sách các Students

 var students = new List
            {
                new Student{Id =1, Name="An", Age=18, Email="[email protected]", Phone="123456789", Score=6.5 },
                new Student{Id =2, Name="Binh", Age=19, Email="[email protected]", Phone="64641313", Score=9.5 },
                new Student{Id =3, Name="Canh", Age=20, Email="[email protected]", Phone="525252525", Score=8 },
                new Student{Id =4, Name="Dat", Age=21, Email="[email protected]", Phone="525252525", Score=5 },
                new Student{Id =5, Name="Em", Age=22, Email="[email protected]", Phone="252525256879", Score=6 },
            };

Tạo một class ConvertToXmlExtension để viết các function convert list to xml

public static class ConvertToXmlExtension
{
	public static string XmlElementToString<T>(this IEnumerable<T> items)
	{
		var xEle = new XElement("Root",
			  from item in items
			  select new XElement("Row", ToXElement(item)));
		return xEle.ToString(SaveOptions.None);
	}

	public static string XmlAttributeToString<T>(this IEnumerable<T> items)
	{
		var xEle = new XElement("Root",
			  from item in items
			  select new XElement("Row", ToXAttribute(item)));
		return xEle.ToString(SaveOptions.None);
	}

	private static IEnumerable<XElement> ToXElement<T>(T item)
	{
		var elements = new List<XElement>();
		var listProperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => !Attribute.IsDefined(x, typeof(XmlIgnoreAttribute)));

		foreach (var prop in listProperties)
		{
			var value = prop.GetValue(item, null);
                        if(value != null)
                        {
			    elements.Add(new XElement(prop.Name, value));
                        }
		}
		return elements;
	}

	private static IEnumerable<XAttribute> ToXAttribute<T>(T item)
	{
		var attributes = new List<XAttribute>();
		var listProperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => !Attribute.IsDefined(x, typeof(XmlIgnoreAttribute)));

		foreach (var prop in listProperties)
		{
			var value = prop.GetValue(item, null);
                        if(value != null) 
                        {
			   attributes.Add(new XAttribute(prop.Name, value));
                        }
		}
		return attributes;
	}
}

Kết quả sau khi gọi function XmlElementToString

var xml = students.XmlElementToString();
Console.WriteLine(xml);
<Root>
  <Row>
    <Id>1</Id>
    <Name>An</Name>
    <Age>18</Age>
    <Phone>123456789</Phone>
    <Email>[email protected]</Email>
    <Score>6.5</Score>
  </Row>
  <Row>
    <Id>2</Id>
    <Name>Binh</Name>
    <Age>19</Age>
    <Phone>64641313</Phone>
    <Email>[email protected]</Email>
    <Score>9.5</Score>
  </Row>
  <Row>
    <Id>3</Id>
    <Name>Canh</Name>
    <Age>20</Age>
    <Phone>525252525</Phone>
    <Email>[email protected]</Email>
    <Score>8</Score>
  </Row>
  <Row>
    <Id>4</Id>
    <Name>Dat</Name>
    <Age>21</Age>
    <Phone>525252525</Phone>
    <Email>[email protected]</Email>
    <Score>5</Score>
  </Row>
  <Row>
    <Id>5</Id>
    <Name>Em</Name>
    <Age>22</Age>
    <Phone>252525256879</Phone>
    <Email>[email protected]</Email>
    <Score>6</Score>
  </Row>
</Root>
var xml = students.XmlAttributeToString();
Console.WriteLine(xml); 
<Root>
  <Row Id="1" Name="An" Age="18" Phone="123456789" Email="[email protected]" Score="6.5" />
  <Row Id="2" Name="Binh" Age="19" Phone="64641313" Email="[email protected]" Score="9.5" />
  <Row Id="3" Name="Canh" Age="20" Phone="525252525" Email="[email protected]" Score="8" />
  <Row Id="4" Name="Dat" Age="21" Phone="525252525" Email="[email protected]" Score="5" />
  <Row Id="5" Name="Em" Age="22" Phone="252525256879" Email="[email protected]" Score="6" />
</Root>

Thực hiện thao tác với XML trong SQL Server

Đối với XML Element


DECLARE @XMLData XML
SET @XMLData ='
<Root>
  <Row>
    <Id>1</Id>
    <Name>An</Name>
    <Age>18</Age>
    <Phone>123456789</Phone>
    <Email>[email protected]</Email>
    <Score>6.5</Score>
  </Row>
  <Row>
    <Id>2</Id>
    <Name>Binh</Name>
    <Age>19</Age>
    <Phone>64641313</Phone>
    <Email>[email protected]</Email>
    <Score>9.5</Score>
  </Row>
  <Row>
    <Id>3</Id>
    <Name>Canh</Name>
    <Age>20</Age>
    <Phone>525252525</Phone>
    <Email>[email protected]</Email>
    <Score>8</Score>
  </Row>
  <Row>
    <Id>4</Id>
    <Name>Dat</Name>
    <Age>21</Age>
    <Phone>525252525</Phone>
    <Email>[email protected]</Email>
    <Score>5</Score>
  </Row>
  <Row>
    <Id>5</Id>
    <Name>Em</Name>
    <Age>22</Age>
    <Phone>252525256879</Phone>
    <Email>[email protected]</Email>
    <Score>6</Score>
  </Row>
</Root>'
SELECT Id = Node.Data.value('(Id)[1]', 'INT')
        , [Name] = Node.Data.value('(Name)[1]', 'VARCHAR(MAX)')
        , [Age] = Node.Data.value('(Age)[1]', 'INT')
		, [Email] = Node.Data.value('(Email)[1]', 'VARCHAR(MAX)')
		, [Phone] = Node.Data.value('(Phone)[1]', 'VARCHAR(MAX)')
		, [Score] = Node.Data.value('(Score)[1]', 'FLOAT')
FROM    @XMLData.nodes('/Root/Row') Node(Data)

Đối với XML Attribute

DECLARE @XMLData XML

SET @XMLData = '<Root>
  <Row Id="1" Name="An" Age="18" Phone="123456789" Email="[email protected]" Score="6.5" />
  <Row Id="2" Name="Binh" Age="19" Phone="64641313" Email="[email protected]" Score="9.5" />
  <Row Id="3" Name="Canh" Age="20" Phone="525252525" Email="[email protected]" Score="8" />
  <Row Id="4" Name="Dat" Age="21" Phone="525252525" Email="[email protected]" Score="5" />
  <Row Id="5" Name="Em" Age="22" Phone="252525256879" Email="[email protected]" Score="6" />
</Root>'

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT
	,@XMLData

SELECT data.Id
	,data.Name
	,data.Age
	,data.Phone
	,data.Email
	,data.Score
FROM OPENXML(@idoc, '/Root/Row', 1) WITH (
		Id INT
		,Name NVARCHAR(50)
		,Age INT
		,Phone NVARCHAR(50)
		,Email NVARCHAR(50)
		,Score FLOAT
		) AS data

EXEC sp_xml_removedocument @idoc

Kết quả

Chúc các bạn thành công.