Declaring the namespace is necessary because the Demographics XML structure uses typed XML – its XML data is associated with an XML schema.
However, we can use a WITH XML NAMESPACES clause to declare the XML namespace instead – this lets us to declare the namespace only once for the entire code block: We’ve used the nodes() method to drill down (one level) to the location of the ‘Individual Survey‘ node, and then returned the actual values via the XQuery value() method.
Let’s assume that we’ve received a request to create XML data from relevant fields in the Person.
Person table, for the person having Business Entity ID 10001.
When run in the same batch, our query that used the nodes() method cost 54% of the batch, where the value()-method-only query cost just 46%: In light of this, why use the nodes() method at all?
Note that there happens to be an existing XML column in the table – the Demographics field.
A popular method to use for this is to use the OPENXML() function, but XQuery methods can also be engaged to perform the same tasks.
OPENXML() was available to use for shredding before the SQL Server XQuery methods were introduced, and is somewhat faster for larger data operations.
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.