Updating database tables from xml

We used CROSS APPLY to join the node set back to the table.A CROSS APPLY would not have been necessary if we were shredding an XML variable, instead of from an XML column in a table.The nodes() method easily shreds XML data from XML columns as well as from XML variables.

updating database tables from xml-7

Now we have a separate node for every value, and a wrapper root node.

In the first example the resulting XML had the same data, but the values were rendered as attributes. To designate a custom root element, we’ll use the PATH argument instead of AUTO: .

Let’s do just that to demonstrate – we’ll combine all of the store survey data from the Sales.

Store table into one XML structure, for Sales Person ID 282.

Let’s examine some of these tasks that require XML manipulation, using the sample Adventure Works2012 database (Other Adventure Works versions should work OK, but there may be variations in the data and/or table schemas).

One common requirement is to create an XML structure that is based on the schema of an existing table.

We notice that the root node name is the schema and table name (Person. Note that the XML namespace data is included in the nested node. To ‘shred’ means to strip the actual data away from the markup tags, and organize it into a relational format.

For example, shredding is what happens when an XML document is imported into a table, when each node value is mapped to a specific field in the table.

Now that our XML data has been shredded, the results can be stored in a table or combined with other queries.

Keep in mind that using the nodes() method in simple queries can unnecessarily reduce query efficiency.

It often becomes necessary to create, shred, combine, or otherwise reconstitute XML data, in order to make it fit a specific purpose.

Tags: , ,