Are You New To XML Data Type?

XML is an Extensive Markup Language which is still a popular medium to pass data between two platforms or applications. Social Media, RSS feed and API uses XML to pass data back and forth. In order to meet the demand of XML use, Relational Databases have added an XML data type. This data type stores XML data without breaking XML structures. You can INSERT, UPDATE, SELECT XML data like you do with other data type like INT, VARCHAR, DATE etc.

XML may not mean anything to DBA but it means a lot to an application developer. In this article, I will show you how you can create a table with XML data type, Insert into, Select from, and convert your two dimensional table into XML format. This article is for DBA or database developer to manage XML data type and I am sorry pal if you are here to learn about XML.

CREATE XML Table

Create XML Data Type
CREATE TABLE employee
( 
  emp_xml_data  xml
);
Command(s) completed successfully.

We have successfully created a table with XML data type. To insert data to this table, I am going to find a sample XML data online.

INSERT INTO

INSERT INTO employee(emp_xml_data) 
VALUES('<Personnel>
<script/>
<Employee type="permanent">
<Name>Seagull</Name>
<Id>3674</Id>
<Age>34</Age>
</Employee>
<Employee type="contract">
<Name>Robin</Name>
<Id>3675</Id>
<Age>25</Age>
</Employee>
<Employee type="permanent">
<Name>Crow</Name>
<Id>3676</Id>
<Age>28</Age>
</Employee>
</Personnel>');

SELECT FROM

SELECT * FROM employee;
The output is a hyperlink which is formatted to display XML data structure.

Convert 2-D to XML

How do you convert 2 dimensional table data into XML data? For this demo, we have a simple 2-dimensional test table with 4 records.

CREATE TABLE dbo.Test 
(
    column_1 AS 'Computed column ' + column_2, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 rowversion,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.Test (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.Test (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.Test (column_2) 
    VALUES ('Explicit value');
INSERT INTO Test DEFAULT VALUES; 
GO

SELECT column_1, column_2, column_3, column_4
FROM dbo.Test;
GO

Output 2-D Table


Let's see how we can convert 2-D relational table into XML data with MS SQL. We will convert just the way your developer wants?

SELECT column_1, column_2, column_3, column_4
FROM dbo.Test
for xml auto, elements, root('test')

Output Into XML 



That's it!. I know what you are thinking next? You now want to know how to convert XML data into 2 dimensional data, correct? This is totally doable in SQL Server but I have yet to try in Oracle DB. You can do much more with XML data type then I explained you here. Like other data types, XML data type has a limitation of 2 GB. Anything greater than 2 GB will either be truncated or throw an error.

I challenged you to convert XML to 2-D and comment below my post on how you accomplished that.  Hey, if you are here, great, you are no longer new to XML data type!  Feel free to change the title.