by Sheila Zhang

This blog is a continuation from my previous blog: How to Query Using Microsoft SQL Server to Create XML, found here. 

Another great query using Microsoft SQL Server to create XML is using this process:

You can manually define your selected SML format by using the PATH option of the FOR XML clause.  In the PATH mode, the standard XML XPath, which describes the path to the element in the generated XML, defines the column names and aliases. The Path is this defined hierarchical:

- Levels are de-limited with the slash (/) character
- Prefix the alias name with the “at” (@) character to generate attribute-centric XML

Below is a simple SQL illustration.

This creates a single customer level of XML data:

From here, you create XML data with nested elements for the child table by using a sub-query within the PATH mode.  

This example shows the order nested under each customer:

It gives this result:

Now that you know how to create XML from relational data, I can show you how to convert XML to relational tables, which is also referred to as “Shredding XML”.  

I begin with this XML data:

Then, I use this statement to convert the XML data to a data table.

**Please note, I use the third value “11”, however you can try other options such as 1 and 2. 

Attribute-centric mapping refers to 1.  For example, BILNAME and ORDNUMBER will not populate because the XML data defines them as elements.

Element-centric mapping refers to 2.  For example, ID will not populate because the XML data defines it as attributes.

However, if attribute-centric and element-centric mapping refers to 11, then by combining flag 8 with flags 1 and 2, you see all the populated fields.

I hope you continue to learn and use my examples on how to query using Microsoft SQL server and to create XML data in your daily projects.  If you have any questions, please post them in the comments section.