0
Introduction to XML
Posted by Danielle Smith
on
17:18
in
XML
Good afternoon everyone! I hope you all had a great weekend!
This blog post is an introduction to the basics of XML within SQL Server. XML in itself is a very large topic, however I am only going to be covering the basics for now and what will be required in the MCTS 70-433 exam. At a later date I will probably expand on this as my knowledge increases.
Query:
Tabular Result:
XML Result:
FOR XML AUTO is similar to XML raw, only it also supports hierarchy structures. However, it can only cope with 1 path of branches, which may be useful in some instances but not all.
Query:
Tabular Result:
XML Result:
FOR XML EXPLICIT is probably the most complex of all the XML Modes. As you can see from the example below, it can be quite tricky to code and maintain, especially when you have many layers to the hierarchy. However, the formatted output does look quite nice and is easy enough to read.
Query:
Tabular Result:
XML Result:
FOR XML PATH is arguably the most appropriate mode which covers the majority of solutions, plus it is easier to implement and maintain in comparison to XML EXPLICIT.
Query:
Tabular Result:
XML Result:
This blog post is an introduction to the basics of XML within SQL Server. XML in itself is a very large topic, however I am only going to be covering the basics for now and what will be required in the MCTS 70-433 exam. At a later date I will probably expand on this as my knowledge increases.
What is XML?
XML stands for Extensible Markup Language, which is used to transport and store data, not necessarily display it. XML is used in a variety of situations in a database:- To retrieve relational data as an XML document rather than a tabular display of results.
- To pass data into the database as XML.
- Storing and querying against an XML document in the database.
SQL Server XML Modes (With Examples)
There are various XML modes in SQL Server that you should be aware about:
FOR XML RAW
FOR XML RAW is probably one of the easiest methods of implementing XML in SQL. It literally passes out all information that you request in a single line format, with one data row for each input in the database.Query:
Tabular Result:
XML Result:
FOR XML AUTO
FOR XML AUTO is similar to XML raw, only it also supports hierarchy structures. However, it can only cope with 1 path of branches, which may be useful in some instances but not all.
Query:
Tabular Result:
XML Result:
FOR XML EXPLICIT
FOR XML EXPLICIT is probably the most complex of all the XML Modes. As you can see from the example below, it can be quite tricky to code and maintain, especially when you have many layers to the hierarchy. However, the formatted output does look quite nice and is easy enough to read.
Query:
XML Result:
FOR XML PATH
FOR XML PATH is arguably the most appropriate mode which covers the majority of solutions, plus it is easier to implement and maintain in comparison to XML EXPLICIT.
Query:
Tabular Result:
XML Result:
Nesting XML Queries
It is also possible to nest your XML queries in order to produce a hierarchy and a complete XML document.
What Next...
In SQL 2008 onwards, Micrososft have included an XML data type. Tomorrow's blog post will explain:
- What the XML Data Type is.
- What the XML Data Type is used for.
- How the XML Data Type can be used properly and appropriately within your database.
As A Final Word...
Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.
Post a Comment
Please post any feedback or comments here...