How to query on XML column in SQL Server?

Question

I have a table named "Table1" and in the table there are columns titled "Name" and "XMLDefinition".

Name         XMLDefinition
--------------------------
Name1        xmlLink1
Name2        xmlLink2

Inside each XML an example would look similar to this below:

<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <QueryView>
    <QueryKey />
  </QueryView>
  <Description>
  </Description>
  <QueryFields>
    <f />
    <f />
  </QueryFields>
  <FilterFields>
    <f ObjectName="TABLE5" ColumnName="ID">
      <DateFilterTypes />
      <FuzzyDateFilterTypes />
      <MonthDayFilterTypes />
      <Values>
        <v>0</v>
      </Values>
      <TranslatedValues>
        <v>No</v>
      </TranslatedValues>
      <DataType>Boolean</DataType>
    </f>   
    <f ObjectName="TABLE2" ColumnName="USERID">
      <DateFilterTypes />
      <FuzzyDateFilterTypes />
      <MonthDayFilterTypes />
      <Values>
        <v>B80055</v>
      </Values>
      <TranslatedValues>
        <v>B80055</v>
      </TranslatedValues>
      <DataType>String</DataType>
    </f>
  </FilterFields>
</Query>

I'd like to return Name from TABLE1 as long as in the XML content contains where ObjectName = "TABLE2" AND ColumnName = "USERID".

I have tried the below and while it doesn't error out, it returns 0 records:

SELECT
a.Name,
X.Y.value('(f)[1]', 'VARCHAR(MAX)') as Object
FROM TABLE1 a
OUTER APPLY a.XMLDefinition.nodes('Query/FilterFields/f') as X(Y)
WHERE X.Y.value('(ObjectName)[1]', 'VARCHAR(MAX)') = 'TABLE2'
AND X.Y.value('(ColumnName)[1]', 'VARCHAR(MAX)') = 'USERID'

I'm not sure what I am missing as it seems I am drilling down from Query > FilterFields > f and I assume I'd be able to then filter based on the ObjectName and ColumnName here.

Attempt 2 Update:

SELECT Name from TABLE1
WHERE XMLDefinition.value('(/Query/QueryView/Description/QueryFields/FilterFields/f/@ObjectName) [1] ',' varchar(max)') = 'TABLE2'
AND XMLDefinition.value('(/Query/QueryView/Description/QueryFields/FilterFields/f/@ColumnName) [1] ',' varchar(max)') = 'USERID'

After trying this attempt by drilling through each tag, it is still giving me 0 results.

Attempt 3 Update:

select
  a.Name,
  X.Y.query(N'.') as [Object] --this returns the XML of the <f> element
from dbo.Table1 a
cross apply a.XMLDefinition.nodes('//*:f[@ObjectName="TABLE2"][@ColumnName="USERID"][1]') as X(Y);

I'm not sure why, but I tried this and now it worked and returned the results that I was looking for. I'm new to XML, but I assume this worked because it ignored all the namespaces and prior tags before the f tag?

Yes, it's totally possible. It's achieved using XPath expressions with the nodes() Method (xml Data Type) and query() Method (xml Data Type) methods. — AlwaysLearning
– AlwaysLearning, Commented Feb 13, 2021 at 0:36
Please do some research, have an attempt, and post when stuck. — Dale K
– Dale K, Commented Feb 13, 2021 at 2:53
The XPath you've edited into "Attempt 2 Update" isn't going to work for you because QueryView, Description and QueryFields are not in the path required to get to /Query/FilterFields/f - they are siblings of FilterFields, not parents of it. — AlwaysLearning
– AlwaysLearning, Commented Feb 14, 2021 at 0:21
I posted an attempt 3 and this time it worked. Using your original answer, I changed the last line to be CROSS APPLY a.XMLDefinition.nodes('//*:f[@ObjectName="TABLE2"][@ColumnName="USERID"][1]') as X(Y). Still a bit confused as to why this worked though and not the previous way. — jwalls91
– jwalls91, Commented Feb 14, 2021 at 1:38
wrt. Attempt 3 Update... yes, namespaces are important. If //f... didn't work but //*:f... did (using a wildcard namespace) that's because the f element isn't in the anonymous namespace like it is in the posted XML example. Wildcard namespaces can work, but they're not particularly efficient, and you sometimes get unexpected results if the same element name is declared in multiple namespaces (which is permitted). — AlwaysLearning
– AlwaysLearning, Commented Feb 14, 2021 at 3:29

AlwaysLearning · Accepted Answer · 2021-02-13 05:49:48Z

1

The code below probably does what you're looking for. Note that cross apply will only return dbo.Table rows that matched the XPath query, as opposed to outer apply which will return all dbo.Table rows but only XML-derived values for those rows that matched the XPath query:

create table dbo.Table1 (
  Name nvarchar(10),
  XMLDefinition xml
);

insert dbo.Table1 (Name, XMLDefinition) values
  (N'Name1', N'<xmlLink1 />'),
  (N'Name2', N'<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema">  
  <FilterFields>
    <f ObjectName="TABLE2" ColumnName="USERID" ParentPath="TABLE2" DisplayPath="TABLE2" CompareType="Or" UseLeftParenthesis="true" LeftParenthesisCount="1" IncludeCurrentNode="true">
      <DateFilterTypes />
      <FuzzyDateFilterTypes />
      <MonthDayFilterTypes />
      <Values>
        <v>B80055</v>
      </Values>
      <TranslatedValues>
        <v>B80055</v>
      </TranslatedValues>
      <DataType>String</DataType>
    </f>
  </FilterFields>
</Query>');

select
  a.Name,
  X.Y.query(N'.') as [Object] --this returns the XML of the <f> element
from dbo.Table1 a
cross apply a.XMLDefinition.nodes(N'/Query/FilterFields/f[@ObjectName="TABLE2"][@ColumnName="USERID"][1]') as X(Y);

answered Feb 13, 2021 at 5:49

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

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

7 Comments

jwalls91 Over a year ago

I tried your sample code and it did work but when I ran it against my actual data it still gave 0 records for some reason. I updated the XML content above to show it's entire hierarchy as I initially left out a few parts. So based on this hierarchy updated above, it looks like CROSS APPLY a.XMLDefinition.nodes(N'/Query/FilterFields/f[@ObjectName="TABLE2"][@ColumnName="USERID"][1]') as X(Y); should still work, but giving 0 records still. Should it now be Query > QueryView > Description > QueryFields > FilterFields > f ?

AlwaysLearning Over a year ago

The data you've edited into your question isn't valid XML since there are a few problems with it: 1. the Query tag has an extra > symbol; 2. the f tags are missing > symbols. Was this due to editing errors, or are you storing the data in varchar/nvarchar columns because it can't be saved into xml columns (because it's invalid)?

jwalls91 Over a year ago

I apologize for those errors, I have fixed them above. After looking at the actual XML you are correct about those > symbols, it was editing errors on my part. It looks like CROSS APPLY a.XMLDefinition.nodes(N'/Query/FilterFields/f[@ObjectName="TABLE2"][@ColumnName="USERID"][1]') as X(Y); should still work though is that right? Or would I need to change the way I'm drilling down to get to the f tag?

AlwaysLearning Over a year ago

The XPath query in nodes() already gets you down to the f tag. The Object column in the results returns the entire XML of the f tag already. Was there something specific about the f tag you needed to return instead, such as the content of the first Values/v tag?

jwalls91 Over a year ago

My objective is mainly to return the Name column from TABLE1 as long as the XMLDefinition column contents contains TABLE2 as the ObjectName in the XML and where the ColumnName is equal to USERID in the XML within the f tag

|

Collectives™ on Stack Overflow

How to query on XML column in SQL Server?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related