XML Data in where clause

XML Data in where clause
XML Data in Document Types.
Step1 : Create Table XMLData.

	Use Adventureworks2012
	Go
	Create Table XMLData
	(
		ID Int Identity(1,1),
		XMLResult XML,
		CreateDate DateTime
	)

Step2 : Insert XML Document Types Data.

	Insert Into XMLData Values('<Databases> <Name>master</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>tempdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T16:13:43.170</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>model</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>msdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2012-02-10T21:02:17.770</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQL</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.300</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQLTempDB</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.583</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>temp</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-22T21:16:22.997</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>Adventureworks2012</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T22:12:48.527</create_date> </Databases>',getdate())

Step3 : Where clause for XML Data Type.

	Select * from XMLData
	 Where XMLResult.value('(/Databases/Name)[1]','varchar(20)') = 'master'

XML Data in Element Types.
Step1 : Truncate XML Data table and insert some element type XML data in the same table.

	Truncate Table XMLData
	Insert Into XMLData Values('<root><Databases name="master" recovery_model="3" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="tempdb" recovery_model="3" state_desc="ONLINE" create_date="2013-05-29T17:09:36.863" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="model" recovery_model="1" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="msdb" recovery_model="3" state_desc="ONLINE" create_date="2012-02-10T21:02:17.770" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQL" recovery_model="1" state_desc="ONLINE" create_date="2013-05-16T20:33:13.300" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQLTempDB" recovery_model="3" state_desc="ONLINE" create_date="2013-05-16T20:33:13.583" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="temp" recovery_model="1" state_desc="ONLINE" create_date="2013-05-22T21:16:22.997" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="Adventureworks2012" recovery_model="1" state_desc="ONLINE" create_date="2013-05-27T22:12:48.527" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="SSISDB" recovery_model="1" state_desc="ONLINE" create_date="2013-05-29T17:07:05.090" /></root>',getdate())

Step2 : Where clause for XML Data Type.

	Select * from XMLData Where XMLResult.exist('(/root/Databases[@name="master"])') = 1
	Select * from XMLData Where XMLResult.value('(/root/Databases/@name)[1]','varchar(20)') = 'master'

Regards,
Nirav Gajjar