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: SQL, Databases, SQL Server, SQL Server 2005
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: SQL, Databases, SQL Server, SQL Server 2005
Comments