XML Data Type Methods

XML Data Type Methods

The XML data type was first introduced with SQL Server 2005. This data type continues with SQL Server 2008 where expanded XML features are available, most notably is the power of the XQuery language to analyze and query the values contained in your XML instance.

There are five XML data type methods available in SQL Server 2008:

1. query() – Used to extract XML fragments from an XML data type.
2. value() – Used to extract a single value from an XML document.
3. exist() – Used to determine if a specified node exists. Returns 1 if yes and 0 if no.
4. modify() – Updates XML data in an XML data type.
5. node() – Shreds XML data into multiple rows (not covered in this blog post).

XML data type methods require an XPath expression as one of the (or the only) XQuery parameter(s), but not all of the methods return data. Some of these methods simply analyze the data at that level and return a status to you. In order to be proficient with any of the XML data type methods, it is important for you to become familiar with using XPath expressions.

The query() Method

This method basically needs an XPath expression in the XQuery parameter and returns an XML data type. The XPath expression (‘/Music/Song[1]/Singer[2]/BandName’) specifies that we want to navigate to the BandName element of the second Singer of the first Song. The query( ) method returns the XML fragment containing everything between (and including) the beginning and ending tags of that BandName element, which in this example is UB40.

By left-clicking on the hyperlink result, a new XML query window launches within SQL Server Management Studio displaying the full XML result set.

Essentially the query( ) method only returns XML data — if you need a single name or value, then you must add text( ) to the XPath expression in your query( ) parameter.

Review the screenshot below to see how the text( ) function pulls out just the BandName value (stripping off the element tags and only displaying the remaining text).

 The value() Method

The next method to explore is value( ). Previously, the query( ) method was used in combination with the text( ) function to pull out just the element text for a single value. Recall that it pulled out the data converted to text (not to XML). When pulling out numerical data utilizing the text() function all data is formatted as text. What if you need a data type other than text?

The value( ) method achieves the same goal as query( ) and text( ) do together, except it allows you to specify the data type you want for your result. It returns just your data (without the metadata – no element tags) and gives you the freedom to specify any data type you would like. In other words, if you are pulling from a element, then you might want the result returned as a Money or a Decimal data type. If you are pulling from the element, you might specify that the returning data should be a varchar data type.

You will find that this method provides an efficient way to retrieve data directly from an XML file and return it to SQL Server. The query syntax is very similar to the previous example. You will use value( ) to return the same data (first Singer of the first Song) by substituting “value” in place of “query” and run the code.

The resulting error message shown in the screenshot below is a reminder to supply a second parameter. The value( ) method requires two parameters.

Observe that XQuery gives us the freedom to specify data types which are compatible with character data (e.g., char(20), varchar(max)).

The exist() Method

In this method you do not want any data returned from the XML stream. You just want a simple check to know whether it is there. The exist( ) method will check for the existence and even the value of the XPath expression you specify.

In this example you will use the exist( ) method to determine if a particular song is in the catalogue. The song TitleID you are looking for is 13161. As you can see from the screenshot, this TitleID does exist as indicated by the return value of 1 (numeric value for ‘Yes’).

You can now modify your code to look for TitleID 13162. A quick look at the screenshot clearly shows that this song is not in the catalogue, since the return value is a 0 (numeric value for ‘No’).

The modify() Method

The modify( ) method allows you to change values directly in your XML stream. Like all other XML data type methods, it needs an XPath parameter to know which value to change. However, unlike the other methods, modify( ) works with an UPDATE statement (it will not work with a SELECT statement). Also, modify( ) can only work with one data value at a time, which is a mathematical and programming concept known as a singleton.

Since there is no limit to the number of elements which can be under another element, any given XPath may have many children. For example, the XPath of /week/day shown below has three elements and, therefore, is not a singleton:

However, if you changed your XPath to (/week/day)[1], then you would only get Monday in your result. Again, since each Song can only have one Title, then specifying the singelton of [1] doesn’t change the appearance of your current result but it helps prepare for the next method to explore, which is modify( ).

It is time for your code to pull out an XML fragment for the Title element. While it will not always be needed, you should add a“[1]” to explicitly specify that you only want a single item retrieved.

The modify( ) method has the sole purpose to change a value in an XML file, which is a helpful capability. Suppose an XML document is imported into SQL Server and you found a typo or need to update just one value. It is no longer necessary to rerun the step to bring in the XML document in order to make that change – you can simply use the modify( ) method and write the change directly to the XML stream contained in the SQL Server instance.

In this example the title “Manic Monday” needs to be changed to “Walk Like an Egyptian”. Since modify( ) is going to replace the current title, Manic Monday, with the new title, you need to add some code to handle the change.

The modify( ) method belongs in the SET clause of an UPDATE statement. You need to add an UPDATE statement and change “SELECT MusicDetails.query” to “SET MusicDetails.modify” Perfect – your revised code runs correctly, and you can see the confirmation (1 row(s) affected).

 Now return to your original query (using the SELECT statement) and run it to confirm that the title was updated correctly.

Reference : http://blog.sqlauthority.com/2012/04/27/sql-server-introduction-to-discovering-xml-data-type-methods-a-primer/

 

Export Data From SQL Server 2005 to Microsoft Excel Datasheet

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName.

Run following code in SQL Server Management Studio – Query Editor.
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO
Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

Make sure your spreadsheet is closed during this operation. If it is open it may thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

Error

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Could not find installable ISAM.”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Solution

http://support.microsoft.com/kb/209805