Monday, July 25, 2005

XML data type and Quoted Identifier

The XML data type has 5 methods namely,

1. exist
2. value
3. modify
4. query and
5. nodes

Extract from MSDN:

1. query() : Use this method to query over an XML instance.
2. value() : Use this method to retrieve a value of SQL type from an XML instance.
3. exist() : Use this method to determine if a query returns a nonempty result.
4. modify() : Use this method to specify XML DML statements to perform updates.
5. nodes() : Use this method to shred XML into multiple rows to propagate parts of XML documents into rowsets.

Code Snippet -- Part I

Declare @xmlString XML
Set @xmlString =
'
<Customer>
<Order id="102">
<ProductName>Rice</ProductName>
</Order>
</Customer>
'

Select testTable.testColumn.query('.') as Records
From @xmlString.nodes('/Customer/Order') testTable(testColumn)


On executing the above code snippet it would display the result as follows:
<Order id="102"><ProductName>Rice</ProductName></Order>

Now, let us excute the below line of code
Set Quoted_Identifier Off

After that if we try and execute the Code Snippet -- Part I ... it would fail. It would throw the below shown error message:

Msg 1934, Level 16, State 1, Line 10
SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns.


Thus the bottom line is all the XML data type methods requires Quoted_Identifier to be ON.

Technorati tags: , , ,

No comments: