Use a Like Statement on SQL Server Xml Datatype

Use a LIKE statement on SQL Server XML Datatype

You should be able to do this quite easily:

SELECT * 
FROM WebPageContent 
WHERE data.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE 'XYZ%'

The .value method gives you the actual value, and you can define that to be returned as a VARCHAR(), which you can then check with a LIKE statement.

Mind you, this isn't going to be awfully fast. So if you have certain fields in your XML that you need to inspect a lot, you could:

• create a stored function which gets the XML and returns the value you're looking for as a VARCHAR()
• define a new computed field on your table which calls this function, and make it a PERSISTED column

With this, you'd basically "extract" a certain portion of the XML into a computed field, make it persisted, and then you can search very efficiently on it (heck: you can even INDEX that field!).

Marc

LIKE comparison in SQL Server XML datatype

If you want to select the value of the Value attribute instead of the whole NameValuePair element with the XPath expression in [XmlDataColumn].value(...), this should work:

/NameValuePairCollection/NameValuePair[@Name="Foo"]/@Value

Your expression only checks if the NameValuePair has an attribute Value.

If there are multiple elements with the correct name and you want to check if any of them has the value "One", you can use the exist(...) method:

where [XmlDataColumn].exist(
  '/NameValuePairCollection/NameValuePair[@Name="Subject" and @Value="One"]') = 1

Find a string with in a TEXT type column that contains an XML file and replace a value in SQL Server

You can use the .modify() XML function for this, using the replace value of syntax

UPDATE users
SET Settings.modify('replace value of (owner/product/@region)[1] with "East"');

db<>fiddle

This only works on one XML document per row.

If you actually have all these nodes in one big blob, you need to run it in a loop

DECLARE @tries int = 0;
WHILE @@ROWCOUNT > 0 AND @tries < 1000
BEGIN
    UPDATE users
    SET Settings.modify('replace value of (owner/product/@region[. != "East"])[1] with "East"')
    WHERE Settings.exist('owner/product/@region[. != "East"]') = 1;
    SET @tries += 1;
END;

db<>fiddle

How can I query a value in SQL Server XML column

select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

In case your column is not XML, you need to convert it. You can also use other syntax to query certain attributes of your XML data. Here is an example...

Let's suppose that data column has this:

<Utilities.CodeSystems.CodeSystemCodes iid="107" CodeSystem="2" Code="0001F" CodeTags="-19-"..../>

... and you only want the ones where CodeSystem = 2 then your query will be:

select 
  [data] 
from
  [dbo].[CodeSystemCodes_data]
  
where
  CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

These pages will show you more about how to query XML in T-SQL:

Querying XML fields using t-sql

Flattening XML Data in SQL Server

EDIT

After playing with it a little bit more, I ended up with this amazing query that uses CROSS APPLY. This one will search every row (role) for the value you put in your like expression...

Given this table structure:

create table MyTable (Roles XML)

insert into MyTable values
('<root>
   <role>Alpha</role>
   <role>Gamma</role>
   <role>Beta</role>
</root>')

We can query it like this:

select * from 

(select 
       pref.value('(text())[1]', 'varchar(32)') as RoleName
from 
       MyTable CROSS APPLY

       Roles.nodes('/root/role') AS Roles(pref)
)  as Result

where RoleName like '%ga%'

You can check the SQL Fiddle here: http://sqlfiddle.com/#!18/dc4d2/1/0

---

Related Topics