Assuming I have a table data as below:
I want to Select all Value
(XML Data) which contains the node Name="Hello World"
. How can I achieve it?
SQL Fiddle
set @f = @XML.exist('/ArrayOfFilterColumn/SelectColumn[(@Name) eq "Hello World"]');
select @f;
I am not sure how could I add it in my where condition, so I have put it in a fiddle.
The exist() method in the WHERE clause returns 1 (True) if the XML does not include any < Specifications > element. Note the use of the not() function (XQuery). The sql:column() function (XQuery) function is used to bring in the value from a non-XML column.
XML data can be queried using an SQL fullselect or with the SQL/XML query functions of XMLQUERY and XMLTABLE. The XMLEXISTS predicate can also be used in SQL queries on XML data.
SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column.
Skip the use of an XML variable and put the exist in the where clause when you query the table.
select F.Value
from XML_FILES as F
where F.Value.exist('/ArrayOfArrayOfSelectColumn/SelectColumn[@Name eq "Hello World"]') = 1
Your column is apparently text
so you need to change that because text
is deprecated and has been for quite some time.
ntext, text, and image (Transact-SQL)
ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
In your case you should of course change to XML instead.
Until you fix that you can cast to XML in your query.
select F.Value
from XML_FILES as F
where cast(F.Value as xml).exist('/ArrayOfArrayOfSelectColumn/SelectColumn[@Name eq "Hello World"]') = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With