You are here

C# object to XML to SQL database

Submitted by Asif Nowaj, Last Modified on 2019-12-09

This articles explains how to store data xml to sql database. There are two steps to achieve XML to SQL by converting a C# object into a XML and then parse the XML within a Stored Procedure of SQL database and store the values to SQL tables. Hence convert xml to sql table can be achieved.
Below illustrations will show you the following.

1. How to convert a C# object into a XML

2. How to parse the XML within a Stored Procedure of SQL database

1. How to convert a C# object into a XML

To illustrate this, let's create some C# classes first. For more clarity, we will create a class which has another class member.


public class Category
{
    public string Id { get; set; }
    public string Name { get; set; }
    public List SubCategories { get; set; }
}
public class SubCategory
{
    public string Id { get; set; }
    public string Name { get; set; }
}

Then let's create some objects using above classes and have list of Categories i.e. List<Category>.


List SubCategories1 = new List();

SubCategory subCategory1 = new SubCategory();
subCategory1.Id = "1234";
subCategory1.Name = "L01";
SubCategories1.Add(subCategory1);

SubCategory subCategory2 = new SubCategory();
subCategory2.Id = "1235";
subCategory2.Name = "L02";
SubCategories1.Add(subCategory2);

List SubCategories2 = new List();

SubCategory subCategory3 = new SubCategory();
subCategory3.Id = "2345";
subCategory3.Name = "L03";
SubCategories2.Add(subCategory3);

List Categories = new List();

Category Category1 = new Category();
Category1.Id = "122";
Category1.Name = "Category L1";
Category1.SubCategories = SubCategories1;
Categories.Add(Category1);

Category Category2 = new Category();
Category2.Id = "121";
Category2.Name = "Category L2";
Category2.SubCategories = SubCategories2;
Categories.Add(Category2);

So now we have List<Category> objects. Now for converting this list of objects into XML, below piece of code can be used.


XmlSerializer xsSubmit = new XmlSerializer(typeof(List));
var xmlString = "";
using (var strignWriter = new StringWriter())
{
    using (XmlWriter writer = XmlWriter.Create(strignWriter))
    {
        xsSubmit.Serialize(writer, Categories);
        xmlString = strignWriter.ToString();
    }
}

Then you get the output like below.


<?xml version="1.0" encoding="utf-16"?><ArrayOfCategory xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Category><Id>122</Id><Name>Category L1</Name><SubCategories><SubCategory><Id>1234</Id><Name>L01</Name></SubCategory><SubCategory><Id>1235</Id><Name>L02</Name></SubCategory></SubCategories></Category><Category><Id>121</Id><Name>Category L2</Name><SubCategories><SubCategory><Id>2345</Id><Name>L03</Name></SubCategory></SubCategories></Category></ArrayOfCategory>

As you can see, the output XML contains XML version and encoding. Also, it contains namespace details.

You might not require these XML version, encoding and namespace details like when you are planning to pass this XML to SQL server database.

Then you can change you code slightly to remove these details along with XML indentation.


XmlSerializer xsSubmit = new XmlSerializer(typeof(List));            
var xmlString = "";
var emptyNamespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });
using (var strignWriter = new StringWriter())
{
    var writerSettings = new XmlWriterSettings();
    writerSettings.Indent = true;
    writerSettings.OmitXmlDeclaration = true;
    using (XmlWriter writer = XmlWriter.Create(strignWriter, writerSettings))
    {
        xsSubmit.Serialize(writer, Categories, emptyNamespaces);
        xmlString = strignWriter.ToString();
    }
}

Then the output will look like below:


<ArrayOfCategory>
  <Category>
    <Id>122</Id>
    <Name>Category L1</Name>
    <SubCategories>
      <SubCategory>
        <Id>1234</Id>
        <Name>L01</Name>
      </SubCategory>
      <SubCategory>
        <Id>1235</Id>
        <Name>L02</Name>
      </SubCategory>
    </SubCategories>
  </Category>
  <Category>
    <Id>121</Id>
    <Name>Category L2</Name>
    <SubCategories>
      <SubCategory>
        <Id>2345</Id>
        <Name>L03</Name>
      </SubCategory>
    </SubCategories>
  </Category>
</ArrayOfCategory>

2. How to parse the XML within a Stored Procedure of SQL database

Now when you have above XML in hand and you want to save this information into the database tables. You can pass this XML as a parameter to a SQL server stored procedure.

You need to parse this XML within the stored procedure to get the data back.

Below is the SQL code sample on how you can parse this XML.


DECLARE @ReportCategoryGradeXML xml   
SET @ReportCategoryGradeXML='<ArrayOfCategory>
  <Category>
    <Id>122</Id>
    <Name>Category L1</Name>
    <SubCategories>
      <SubCategory>
        <Id>1234</Id>
        <Name>L01</Name>
      </SubCategory>
      <SubCategory>
        <Id>1235</Id>
        <Name>L02</Name>
      </SubCategory>
    </SubCategories>
  </Category>
  <Category>
    <Id>121</Id>
    <Name>Category L2</Name>
    <SubCategories>
      <SubCategory>
        <Id>2345</Id>
        <Name>L03</Name>
      </SubCategory>
    </SubCategories>
  </Category>
</ArrayOfCategory>'

SELECT Category.value('(Id/text())[1]', 'int') AS CategoryId,
Category.value('(Name/text())[1]', 'varchar(100)') AS CategoryName,	
SubCategory.value('(Id/text())[1]', 'int') AS SubCategoryId,
SubCategory.value('(Name/text())[1]', 'varchar(100)') AS SubCategoryName	
FROM @ReportCategoryGradeXML.nodes('/ArrayOfCategory/Category') AS a(Category)
CROSS APPLY a.Category.nodes('SubCategories/SubCategory') AS SubCategories(SubCategory)

This is how you can convert a C# object to XML and then store the data into SQL tables to achieve XML to SQL.

The query output will look like
SQL Query Output

Discussion or Comment

If you have anything in mind to share, please bring it in the discussion forum here.

https://forum.everyething.com/sql-server-f38/