Loading XML Documents into SQL Server 2005 - Part 3
This is the third in a series of posts. You can find the previous posts here (1, 2).
So far, I haven't used namespaces in my sample document. I've updated doc2.xml to include a default namespace. If you run the previous sql example against this new document, you will get back nulls instead of the document id and year. This is because the XQuery command uses no namespace, while the document now has one. Since the namespaces don't match, the query fails.
To fix this problem, you can declare the namespace in each XQuery statement. Or, you can use the new "with xmlnamespaces" syntax to define it for every XQuery statement issued within a given select statement.
LoadFile2.sql contains an updated version of the query which uses this new syntax. Note that I needed to add a ";" to the end of the first select statement so that the TSQL compiler would know that the with clause was the beginning of a new statement.
For more info, see the following books online topics:
Adding Namespaces Using WITH XMLNAMESPACES
-David