Select data using WHERE condition on XML data column in SQL table

Question

I have a table that lists some user details.

ID	GUID	Username	Password	Data
1	a2a8s7d4d	xswe	xxxxxx	XML
2	aer335mla	user	xxxxxx	XML

The Data column contains data using XML. Below is a sample from the table.

<UserInfo xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/ComponentFramework">
<ActiveDirectoryUser>false</ActiveDirectoryUser>
<CanUpdateMasterData>false</CanUpdateMasterData>
<CanUploadFiles>false</CanUploadFiles>
<ChangePassword>false</ChangePassword>
<CustomDataPageSize>false</CustomDataPageSize>
<CustomMasterDataPageSize>false</CustomMasterDataPageSize>
<DataPageSize>100</DataPageSize>
<Disabled>true</Disabled>
<Displayname>Pål</Displayname>
<Email i:nil="true" />
<EnforcePasswordPolicy>false</EnforcePasswordPolicy>
<EnvironmentIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<GUID i:nil="true" />
<GeoLocation>
    <City i:nil="true" />
    <Country i:nil="true" />
    <CountryCode i:nil="true" />
    <Ip i:nil="true" />
    <Isp i:nil="true" />
    <Lat>0</Lat>
    <Lon>0</Lon>
    <Org i:nil="true" />
    <Query i:nil="true" />
    <Region i:nil="true" />
    <RegionName i:nil="true" />
    <Status i:nil="true" />
    <Timezone i:nil="true" />
    <Zip i:nil="true" />
</GeoLocation>
<GroupIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<HttpLink i:nil="true" />
<JobIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<LastLoggedIn>2015-06-11T19:04:44.6407074+05:30</LastLoggedIn>
<MasterDataPageSize>1000</MasterDataPageSize>
<ModifyImages>false</ModifyImages>
<QualityControl>false</QualityControl>
<QualityControlGroupId i:nil="true" />
<Review>false</Review>
<ReviewGroupId i:nil="true" />
<SecurityToken i:nil="true" />
<ShowTrackerPage>false</ShowTrackerPage>
<StatIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<Username>&lt;new user&gt;</Username>
<Usertype>Power</Usertype>
</UserInfo>

I'm trying to match users that have their accounts disabled. Using the below sql query.

select * from [ATC_Config].[dbo].[Users] where [ATC_Config].[dbo].[Users].[Data].value('/UserInfo/Disabled[1]','nvarchar(MAX)') = 'true'

But SSMS is giving me an error Cannot call methods on nvarchar(max) and highlight my column which Data. I tried few suggestions in SO and in MSDN but nothing helped. Can someone show me what am I doing wrong?

The error is telling you the problem; clearly you column Data isn't the xml data type. If it's storing XML, it should be the xml data type, and as you've found out, you can't use XQuery methods on an nvarchar column. — Thom A
– Thom A ♦, Commented Mar 11, 2022 at 11:38
@Larnu Thank you for the comment. I have to convert this column to XML first then use the query to search. This is not my DB inherited this. Thanks again. — S4NDM4N
– S4NDM4N, Commented Mar 11, 2022 at 11:46
Your XML sample has a default namespace defined. You'll need to specify that either inside your value query or using with xmlnamespaces. — AlwaysLearning
– AlwaysLearning, Commented Mar 11, 2022 at 11:56

AlwaysLearning · Accepted Answer · 2022-03-11 12:06:08Z

1

Because your sample XML contains a default namespace definition you'll need to declare that in your value XQuery or via with xmlnamespaces.

Here's how you can do that with value...

select *
from dbo.Users
where cast(Data as xml).value(N'
  declare default element namespace "http://schemas.datacontract.org/2004/07/ComponentFramework";
  (/UserInfo/Disabled)[1]',N'nvarchar(max)') = N'true';

Or by using with xmlnamespaces:

with xmlnamespaces(default N'http://schemas.datacontract.org/2004/07/ComponentFramework')
select *
from dbo.Users
where cast(Data as xml).value(N'(/UserInfo/Disabled)[1]', N'nvarchar(max)') = N'true';

answered Mar 11, 2022 at 12:06

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

S4NDM4N Over a year ago

Thank you this worked and learned something new.

Charlieface · Accepted Answer · 2022-03-11 12:38:16Z

1

You need to add the namespace, and you need to cast the value to xml. This is easier if you use with because it applies to the whole query.

A slightly more efficient version of @AlwaysLearning's answer is to use exist and /text()

with xmlnamespaces (
    default N'http://schemas.datacontract.org/2004/07/ComponentFramework'
)
select *
from dbo.Users u
where cast(u.Data as xml).exist(N'/UserInfo/Disabled[text() = "true"]') = 1;

db<>fiddle

I strongly suggest you store the Data column as xml in the first place, as casting is inefficient.

edited Mar 11, 2022 at 12:38

answered Mar 11, 2022 at 12:31

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

2 Comments

S4NDM4N Over a year ago

I agree with your suggestion but this is database is part of a live production system. So kind of having second thoughts of converting the data type. Because I inherited the entire system from the last guy. I just don't want to mess with the live system but have to clean the unwanted users.

S4NDM4N Over a year ago

Both answers are right. If I could mark both as right I would have but marked @AlwaysLearning as the answer and gave you an upvote.

Collectives™ on Stack Overflow

Select data using WHERE condition on XML data column in SQL table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related