Showing posts with label XML PATH. Show all posts
Showing posts with label XML PATH. Show all posts

Friday, August 28, 2020

XML in SQL Server

This is my second post on XML after 10 years. I was contented with the earlier post however I experienced more over these years and learned many things which I wanted to share with the current and future generations.

Let me begin with the basics.

XML is a file extension for an Extensible Markup Language (XML) represents information in a hierarchical (tree) structure in a simple text format. An XML document consists of XML elements and all elements in an XML document can contain sub-elements, text, or attributes. The hierarchical structure represented by an XML document starts at the root element and branches to the lowest level of elements.

There are several modes that change the shape of XML format while generating it through SQL Server using FOR XML clause.

Instead of reinventing the wheel, I would quote the MSDN information here for the descriptions with the examples I prepared.

Here is the data for the tryout.

VideoID VideoDesc vcVideoLocation
842735 SQL D:\Partition23\20200101\73899mpart
842736 Java   D:\Partition23\20200101\74900mpart
842738 XML D:\Partition23\20200101\74901mpart
842739 Hive D:\Partition23\20200101\74901mpart
842740 Sqoop D:\Partition23\20200101\74900mpart
842742 Impala D:\Partition23\20200101\73899mpart
842743 SQL D:\Partition23\20200101\74900mpart
842744 Hadoop D:\Partition23\20200104\74791mpart
842745 SQL D:\Partition23\20200101\74901mpart
842746 Hive D:\Partition23\20200101\73899mpart
842747 Sqoop D:\Partition23\20200104\74791mpart
842748 Impala D:\Partition23\20200101\74900mpart
842749 Spark D:\Partition23\20200101\74900mpart
842750 HBase D:\Partition23\20200101\73899mpart
842751 Scala D:\Partition23\20200104\74791mpart
842752 Hive D:\Partition23\20200104\74791mpart
842753 SQL D:\Partition23\20200101\74901mpart
842756 Impala D:\Partition23\20200101\74901mpart


SQL Code:

CREATE TABLE VideoData(VideoID BIGINT, VideoDesc VARCHAR(10), vcVideoLocation NVARCHAR(MAX))

INSERT INTO VideoData(VideoID, VideoDesc, vcVideoLocation) VALUES
(842735,'SQL','D:\Partition23\20200101\73899mpart'),
(842736,'Java','D:\Partition23\20200101\74900mpart'),(842738,'XML','D:\Partition23\20200101\74901mpart'),(842739,'Hive','D:\Partition23\20200101\74901mpart'),(842740,'Sqoop','D:\Partition23\20200101\74900mpart'),(842742,'Impala','D:\Partition23\20200101\73899mpart'),(842743,'SQL','D:\Partition23\20200101\74900mpart'),(842744,'Hadoop','D:\Partition23\20200104\74791mpart'),(842745,'SQL','D:\Partition23\20200101\74901mpart'),(842746,'Hive','D:\Partition23\20200101\73899mpart'),(842747,'Sqoop','D:\Partition23\20200104\74791mpart'),(842748,'Impala','D:\Partition23\20200101\74900mpart'),(842749,'Spark','D:\Partition23\20200101\74900mpart'),(842750,'HBase','D:\Partition23\20200101\73899mpart'),(842751,'Scala','D:\Partition23\20200104\74791mpart'),(842752,'Hive','D:\Partition23\20200104\74791mpart'),(842753,'SQL','D:\Partition23\20200101\74901mpart'),(842756,'Impala','D:\Partition23\20200101\74901mpart')


XML RAW:

Each row in the result set is taken as one element with your columns being the attributes. 


SELECT
VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML RAW;

If you look at the output, each row from the table is represented as a single element in XML and is also represented by the keyword <row>. As stated in the beginning, the elements can contain sub-elements, text or attributes; here the table’s columns are being treated as attributes. What if the keyword ELEMENTS mentioned after FOR XML RAW? Let’s check it out. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML RAW, ELEMENTS;

Now, each row element has column names as sub-elements. 

FOR XML RAW and FOR XML RAW, ELEMENTS are returning a shape that is slightly different.


XML AUTO:

Returns query results in a simple, nested XML tree. Each table in the FROM clause for which at least one column is listed in the SELECT clause is represented as an XML element. 

Each row of the table will be represented as an element by table name.


SELECT
VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML AUTO; 


Let’s add “ELEMENTS” to it and see how the output changes.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML AUTO, ELEMENTS;

Table name as an element for each row and each element has column names as sub-elements

 

XML PATH:

Provides a simpler way to mix elements and attributes, and to introduce additional nesting for representing complex properties. Table columns are passed as child elements.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH;


If you look at the outcome of “FOR XML RAW, ELEMENTS” and “FOR XML PATH” there is no difference at all. Both are returning the same elements and sub-elements. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH, ELEMENTS; 

Again, the output is as same as “FOR XML RAW, ELEMENTS” and “FOR XML PATH”.


Look at the below example -

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH('VideoInfo');

User can replace the default keyword “row” with any desired information. In the above example, since the data is related to Videos, I mentioned “VideoInfo” for the readability. It solves no other purpose.

Synopsis: RAW will return the table’s row as an element and column names being the attributes. AUTO will return the table’s name as an element and the element contains the entire row and the column names are the attributes. PATH returns the row as element and columns as child or sub-elements.

Now let’s go back to the data; the table contains video information such as on which topic the video is created and where the video is located. There is more than one video on the same subject and there are several videos on the same location. 

We can create different shapes apart from the above by using the existing modes, either clustered/grouped by topic of the video or by the location, similar to bitmap fashion. Look at the below to understand easily.


SELECT
vcVideoLocation AS '@path', (
              SELECT VideoID AS [index], VideoDesc FROM VideoData t1
              WHERE t1.vcVideoLocation = t2.vcVideoLocation
              FOR XML PATH('VideoInfo'),TYPE
                                                          )
              FROM (
                      SELECT DISTINCT vcVideoLocation FROM VideoData) t2
                      FOR XML PATH('vLocation'), ROOT('data') 

“data” is the main element for the whole table data, the location is sub-element; and in each location, there are several videos that are being treated as attributes within the user-defined sub-element “VideoInfo”. Look at the results below.




In the above example, the data is grouped by “location”. Similarly, we can shape it by grouping by “Subject” (i.e. Video Description).

SELECT VideoDesc AS '@Desc', (
              SELECT VideoID AS [index], vcVideoLocation FROM VideoData t1
              WHERE t1.VideoDesc = t2.VideoDesc
              FOR XML PATH('VideoInfo'),TYPE
                                                  )
              FROM (
                      SELECT DISTINCT VideoDesc FROM VideoData) t2
                      FOR XML PATH('VideoType'), ROOT('data')

Output: 




Hope you find this article helpful.


Tuesday, March 16, 2010

SQL Server - Data Export to XML

One of my friend requested me to explain how many ways are there to export the data from SQL Server 2005 into an XML file. Well, this artcle will let you know how to export the data from SQL Server 2005 to XML using different modes.

SELECT * FROM Customers (NOLOCK)

/* Result is
CustomerID CustomerName ActiveStatus
----------- ------------------------------ ------------
100 John 0
200 Kate 1
300 Julia 1
400 Maddy 0

(4 row(s) affected)
*/

SELECT * FROM Customers (NOLOCK)
FOR XML PATH, ROOT('root')

(Please note that I have placed "`" symbol after "<" and ">" in the results to avoid execution in the web) 

/* Result is
<`root`>
<`row`>
<`CustomerID>100<`/CustomerID>
<`CustomerName>John<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/row><`row`>
<`CustomerID>200<`/CustomerID>
<`CustomerName>Kate<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/row><`row>
<`CustomerID>300<`/CustomerID>
<`CustomerName>Julia<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/row><`row>
<`CustomerID>400<`/CustomerID>
<`CustomerName>Maddy<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/row>
<`/root>*/

/**************************************/
/** EXAMPLES FOR AUTO MODE **/
/**************************************/
In order to generate simple hierarchies we can use AUTO mode. Since the result will be in form of nested elements, it doesn't provide much control over the shape of the XML whereas EXPLICIT and PATH modes provide better control and shape of the XML.

SELECT * FROM Customers FOR XML AUTO, TYPE

/* Result is
<`Customers CustomerID="100" CustomerName="John" ActiveStatus="0" /`>
<`Customers CustomerID="200" CustomerName="Kate" ActiveStatus="1" /`>
<`Customers CustomerID="300" CustomerName="Julia" ActiveStatus="1" /`>
<`Customers CustomerID="400" CustomerName="Maddy" ActiveStatus="0" /`>
*/

Using Variables
DECLARE @cust XML;
SET @cust = (SELECT * FROM Customers FOR XML AUTO, TYPE)
SELECT @cust

/* Result is
<`Customers CustomerID="100" CustomerName="John" ActiveStatus="0" /`>
<`Customers CustomerID="200" CustomerName="Kate" ActiveStatus="1" /`>
<`Customers CustomerID="300" CustomerName="Julia" ActiveStatus="1" /`>
<`Customers CustomerID="400" CustomerName="Maddy" ActiveStatus="0" /`>
*/

XML Data is into another table
CREATE TABLE Test1(i int, x XML)

INSERT INTO Test1 SELECT 1, (SELECT * FROM Customers FOR XML AUTO, TYPE)

SELECT * FROM Test1

/* Result is
<'Customers CustomerID="100" CustomerName="John" ActiveStatus="0" /`>
<'Customers CustomerID="200" CustomerName="Kate" ActiveStatus="1" /`>
<'Customers CustomerID="300" CustomerName="Julia" ActiveStatus="1" /`>
<'Customers CustomerID="400" CustomerName="Maddy" ActiveStatus="0" /`>
*/
/************************************/
/** EXAMPLE FOR RAW MODE **/
/************************************/
Each row of the result set from the query will be converted into element. The column from the result set will be mapped to the attribute of the row element.

SELECT *
FROM Customers
FOR XML RAW, ELEMENTS

/* Result is
<`row>
<`CustomerID>100<`/CustomerID>
<`CustomerName>John<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/row><`row>
<`CustomerID>200<`/CustomerID>
<`CustomerName>Kate<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/row><`row>
<`CustomerID>300<`/CustomerID>
<`CustomerName>Julia<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/row><`row>
<`CustomerID>400<`/CustomerID>
<`CustomerName>Maddy<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/row>
*/

You can rename the element by using optional argument in RAW Mode.
SELECT *
FROM Customers
FOR XML RAW ('CustomerDetails'), ELEMENTS

/* Result is
<`CustomerDetails>
<`CustomerID>100<`/CustomerID>
<`CustomerName>John<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/CustomerDetails><`CustomerDetails>
<`CustomerID>200<`/CustomerID>
<`CustomerName>Kate<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/CustomerDetails><`CustomerDetails>
<`CustomerID>300<`/CustomerID>
<`CustomerName>Julia<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/CustomerDetails><`CustomerDetails>
<`CustomerID>400<`/CustomerID>
<`CustomerName>Maddy<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/CustomerDetails>
*/

/*****************************************/
/** EXAMPLE FOR EXPLICIT MODE **/
/*****************************************/
This mode is more recommended one when compare with RAW and AUTO modes. It is because of the control over the shape of the XML.

For more details refer to :
http://msdn.microsoft.com/en-us/library/ms189068.aspx

CREATE VIEW DataExport AS
SELECT
1 AS Tag,
NULL AS Parent,
'CustomerID' AS [data!1!identifier],
NULL AS [record!2!CustomerID!element] ,
NULL AS [record!2!CustomerName!element],
NULL AS [record!2!ActiveStatus!element]

UNION ALL


SELECT
2 AS Tag,
1 AS Parent,
'CustomerID' AS [data!1!identifier],
CustomerID AS [record!2!CustomerID!element] ,
CustomerName AS [record!2!CustomerName!element],
ActiveStatus AS [record!2!ActiveStatus!element]
FROM Customers SELECT * FROM DataExport
FOR XML EXPLICIT

/* Result is
<`data identifier="CustomerID">
<`record>
<`CustomerID>100<`/CustomerID>
<`CustomerName>John<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/record>
<`record>
<`CustomerID>200<`/CustomerID>
<`CustomerName>Kate<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/record>
<`record>
<`CustomerID>300<`/CustomerID>
<`CustomerName>Julia<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/record>
<`record>
<`CustomerID>400<`/CustomerID>
<`CustomerName>Maddy<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/record>
<`/data>
*/

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...