How to query xml column in TSQL for specific text

Question

I am trying to query the word Simple out the following XML Document. The column is xml data type, the column name is InstanceSecurity.

<InstanceSecurity>
  <Folder>
    <Authorization Mode="Simple">
      <Deny PrivilegeCode="Create" />
      <Deny PrivilegeCode="Delete" />
      <Deny PrivilegeCode="Edit" />
      <Deny PrivilegeCode="Read" />
      <Deny PrivilegeCode="View" />
      <Deny PrivilegeCode="Print" />
      <Allow PrivilegeCode="All" EntityType="Personnel" EntityVal="5bc2" />
      <Allow PrivilegeCode="All" EntityType="Personnel" EntityVal="f85b" />
    </Authorization>
  </Folder>
</InstanceSecurity>

I've tried:

Select InstanceSecurity.query('/InstanceSecurity/Folder/Authorization[@Mode="Simple"]') AS SecuredMode
FROM Cases

AND

SELECT *
FROM Cases
WHERE InstanceSecurity.value('(/InstanceSecurity/Folder/Authorization/Mode)[1]','nvarchar(100)') like '%'

Neither one returns anything

Most examples I have looked at have a start and end tag, like

<item id="a">a is for apple</item>

not sure if that has any effect on how to query this.

Any help would be appreciated.

Shnugo · Accepted Answer · 2018-05-04T09:11:19.253

It is very important to understand, what content you want to pick out of an XML. There are elements and attributes - and the floating text between element tags, the text() node. Execute this to see the principles:

DECLARE @xml XML=
N'<root>
    <AnElement ID="1" AnAttribute="The attribute''s content">The element''s content</AnElement>
    <AnElement ID="2" AnAttribute="Only the attribute" />
    <AnElement ID="3">Text only</AnElement>
    <AnElement ID="4" AnAttribute="nested children">
        <child>This is the child''s content</child>
        <child>One more child</child>
    </AnElement>
    <AnElement ID="5" AnAttribute="nested children">
        This is text 1 within the element
        <child>This is the child''s content</child>
        This is text 2 within the element
        <child>One more child</child>
        This is text 3 within the element
    </AnElement>
</root>';

SELECT elmt.value(N'@ID',N'int') ID
      ,elmt.value(N'@AnAttribute',N'nvarchar(250)') TheAttribute
      ,elmt.value(N'text()[1]',N'nvarchar(250)') TheFirstText
      ,elmt.value(N'text()[2]',N'nvarchar(250)') TheSecondText
      ,elmt.value(N'child[2]/text()[1]',N'nvarchar(250)') TheFirstTextWithinChild2
      ,elmt.value(N'.',N'nvarchar(250)') AS FullNodeContent
FROM @xml.nodes(N'/root/AnElement') AS A(elmt);

Hint: Try to pick just the last one (value(N'.'),N'nvarchar(250)') and send the output to plain text. This includes spaces and line breaks!

The text() (there may be more text() nodes within one element!) is just another kind of node, coming without surrounding tags.

Your attempts (slightly changed)

Select @xml.query('/InstanceSecurity/Folder/Authorization[@Mode="Simple"]')

This returns all <Authorization> nodes (xml typed!) where the attribute "Mode" has the content "Simple"

SELECT @xml.value('(/InstanceSecurity/Folder/Authorization/@Mode)[1]','nvarchar(100)')
(You forgot the @ before "Mode"!)

This returns the value of the attribute. In the case above this is "Simple"

About your issue

I do not really know what you want, but one of the following should point you the way

--Returns the first value of attribute @Mode at the given path

SELECT InstanceSecurity.value(N'(/InstanceSecurity/Folder/Authorization/@Mode)[1]',N'nvarchar(250)')
FROM Cases;

--If your XML might include more than one <Authorization>, this would return all their @Mode attributes

SELECT auth.value(N'@Mode',N'nvarchar(250)')
FROM Cases
OUTER APPLY InstanceSecurity.nodes(N'/InstanceSecurity/Folder/Authorization') AS A(auth);

--This returns - in cases of many - all <Authorization> elements where the @Mode is "Simple"

SELECT auth.query(N'.')
FROM Cases
OUTER APPLY InstanceSecurity.nodes(N'/InstanceSecurity/Folder/Authorization[@Mode="Simple"]') AS A(auth);

--And this returns all table's rows where at least one @Mode has the value "Simple"

SELECT *
FROM Cases
WHERE InstanceSecurity.exist(N'/InstanceSecurity/Folder/Authorization[@Mode="Simple"]')=1

Final hint: You can replace a literal value within XQuery with sql:variable("@VarName") or with sql:column("ColumnName").

Thanks Shnugo, I am just learning XML, and don't have a good sense of all the pieces/parts yet, still reading the book. What my ultimate goal here was to figure out which cases are secured, so that I don't include them in my SSRS reports. Secured cases have the XML similar to my example above, unsecured cases have the following XML, , I was able to read for the existence of the deny tag, which tells me the case is secured. but I wanted to read the word Simple and couldn't figure out how to do it. — Christopher Didamo DCSO, May 04 '18 at 23:09

score 0 · Answer 2 · answered May 04 '18 at 08:15

0

Try this

SELECT * FROM Cases
WHERE InstanceSecurity.value('(/InstanceSecurity/Folder/Authorization/@Mode)[1]','varchar(100)') = 'Simple'

answered May 04 '18 at 08:15

Michał Turczyn

32,028
14
47
69

1

I do not think, that this really is what the OP needs. If this is the correct return, you should rather use `XML.exist()`. Your approach has to shred each and any XML, just to filter most of this away at the end... `.exist()` is optimized for performance... – Shnugo May 04 '18 at 08:56
@Shnugo I have always quried a particular value from `XML`s and wasn't familiar with such method. Nice to know that, thanks :) – Michał Turczyn May 04 '18 at 09:03

How to query xml column in TSQL for specific text

2 Answers2

About your issue

Linked