TSQL - Need to query a database column which is populated by XML

Question

TSQL - Need to query a database column which is populated by XML.

The Database has an iUserID column with an Application ID and VCKey

TxtValue is the Column name and the contained Data is similar to this

<BasePreferencesDataSet xmlns="http://tempuri.org/BasePreferencesDataSet.xsd">
<ViewModesTable>
<iViewID>1</iViewID>
</ViewModesTable>
<ViewMode_PreferenceData>
<iViewID>1</iViewID>
<iDataID>0</iDataID>
<strValue>False</strValue>
</ViewMode_PreferenceData>
<ViewMode_PreferenceData>
<iViewID>1</iViewID>
<iDataID>5</iDataID>
<strValue>True</strValue>
</ViewMode_PreferenceData>
<ViewMode_PreferenceData>
<iViewID>1</iViewID>
<iDataID>6</iDataID>
<strValue>True</strValue>
</ViewMode_PreferenceData> 
<ViewMode_PreferenceData>
<iViewID>1</iViewID>
<iDataID>4</iDataID>
<strValue>False</strValue>

I want to be able to identify any iUserID in which the StrValue for iDataID's 5 and 6 are not set to True.

I have attempted to use a txtValue Like % statement but even if I copy the contents and query for it verbatim it will not yield a result leading me to believe that the XML data cannot be queried in this manner.

Screenshot of Select * query for this DB for reference

Stack overflow website changed the contained data example due to the brackets etc. Please reference the screenshot to understand how the data is stored in the txtValue Column — Chris
– Chris, Commented Aug 24, 2018 at 21:15
The mini-markdown formatting on the page makes it impossible to cut and paste the data and keep it in its native form. I think I am only able to add a screenshot on the initial post, so I am trying to come up with another alternative here — Chris
– Chris, Commented Aug 25, 2018 at 0:43
Perhaps you could post the example DDL and data at SQL Fiddle. — HABO
– HABO, Commented Aug 25, 2018 at 2:21
@Chris: Take the text representation of your data and indent if by four spaces. Check your favorite editor, it might be provide a function to indent the text (or a selection) (but make sure you use spaces not tabs) or maybe some regular expression find and replace, that lets you replace the beginning of each line with four spaces. Then edit your question and post that indented text. It will be shown verbatim, in a nice code box. — sticky bit
– sticky bit, Commented Aug 25, 2018 at 3:54

Gottfried Lesigang · Accepted Answer · 2018-08-27 13:02:43Z

1

You can try XML-method .exist() together with an XPath with predicates:

WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/BasePreferencesDataSet.xsd') 
SELECT * 
FROM YourTable
WHERE CAST(txtValue AS XML).exist('/BasePreferencesDataSet
                                   /ViewMode_PreferenceData[iDataID=5 or iDataID=6]
                                   /strValue[text()!="True"]')=1;

The namespace-declaration is needed to address the elements without a namespace prefix.

The <ViewMode_PreferenceData> is filtered for the fitting IDs, while the <strValue> is filtered for a content !="True". This will return any data row, where there is at least one entry, with an ID of 5 or 6 and a value not equal to "True".

answered Aug 27, 2018 at 13:02

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Comments

Stuart Ainsworth · Accepted Answer · 2018-08-25 04:22:07Z

0

So without sample data (including tags; sorry you're having trouble with that) it's tough to craft the complete query, but what you're looking for is XQuery, specifically the .exists method in T-SQL.

Something like

SELECT iUserID
FROM tblLocalUserPreferences
WHERE iApplicationID = 30 
AND vcKey='MonitorPreferences'
AND (txtValue.exist('//iDataID[text()="5"]/../strValue[text()="True"]') = 0
      OR txtValue.exist('//iDataID[text()="6"]/../strValue[text()="True"]')=0)

This should return all userID's where either iDataID 5 or 6 do NOT contain True. In other words, if both are true, you won't get that row back.

edited Aug 25, 2018 at 4:22

answered Aug 24, 2018 at 21:27

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

8 Comments

Chris Over a year ago

Looking for another way to add an image, If I cut and paste the data is skewed as a result of the mini-markdown formatting on the website. I will try to paste it again in this comment. <BasePreferencesDataSet xmlns="tempuri.org/BasePreferencesDataSet.xsd"> <ViewModesTable> <iViewID>1</iViewID> </ViewModesTable> <ViewMode_PreferenceData> <iViewID>1</iViewID> <iDataID>0</iDataID> <strValue>False</strValue> </ViewMode_PreferenceData> <ViewMode_PreferenceData> <iViewID>1</iViewID> <iDataID>5</iDataID> </ViewMode_PreferenceData>

Stuart Ainsworth Over a year ago

looks like it made it; give me a few.

Stuart Ainsworth Over a year ago

so in the sample xml you sent, there is no strValue for iDataID 5. Would you want to see the iUserID for that condition (in other words, it's not set to True)?

Stuart Ainsworth Over a year ago

..and, what happens if there is no dataID of 6? Do both conditions have to be missing or not True to show up? If ID 5 = True and ID 6 = False, what happens?

Chris Over a year ago

the data did not fit in the comment size constraints. I was just trying to get an actual representation of the data into the comment. Both fields are always populated and visible with either a true or false within the strValue.

|

Collectives™ on Stack Overflow

TSQL - Need to query a database column which is populated by XML

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related