Loading XML Documents into SQL Server 2005 - Part 2
This is the second in a series of posts. You can find the first post here.
Now that we've loaded our XML document into a variable, we need to extract the id and year information from it. As can be seen in the following sample, this is done use the "value" method of the XML data type. To keep the code short, I use the select statement. You would want to insert the results into a table.
The value method takes two parameters. The first is an XQuery statement, the second is the SQL data type of the value that will be returned.
In this example, the XQuery statement is just an XPath expression. The one syntax element which may seem confusing is the "[1]". Technically, the XPath expression could return more than one value. The [1] tells the compiler that the result of the expression will be a singleton. If you don't use [1], you will get the following error:
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
Finally, keep in mind that the slashes used in an XPath expression are different from the slashes used in a Windows file path expression.
For more info, see the following books online topics:
value() Method (xml Data Type)
-David