如何在TSQL中查询特定文本的xml列 [英] How to query xml column in TSQL for specific text

问题描述

我正在尝试从以下 XML 文档中查询单词 Simple.列是xml数据类型，列名是InstanceSecurity.

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>

我试过了:

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

和

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.

任何帮助将不胜感激.

推荐答案

了解您想从 XML 中挑选出哪些内容非常重要.有 elements 和 attributes - 以及元素标签之间的浮动 text，text() 节点.执行此操作以查看原理:

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);

提示: 尝试只选择最后一个 (value(N'.'),N'nvarchar(250)') 并将输出发送到纯文本.这包括空格和换行符！

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!

text()(一个元素中可能有更多的 text() 节点！)只是另一种节点，即将到来没有周围的标签.

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

您的尝试(略有变化)

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

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

这将返回所有 节点(xml 类型！)，其中属性Mode"的内容为Simple"

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

选择@xml.value('(/InstanceSecurity/Folder/Authorization/@Mode)[1]','nvarchar(100)')
(你忘记了模式"前的 @！)

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"

我真的不知道你想要什么，但以下之一应该为你指明方向

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

--返回给定路径处属性@Mode的第一个值

--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;

--如果您的 XML 可能包含多个 ，这将返回它们的所有 @Mode 属性

--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);

--这将返回 - 在许多情况下 - 所有 元素，其中 @Mode 为简单"

--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);

--这将返回表中至少有一个 @Mode 值为 "Simple"

--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

最终提示:您可以用 sql:variable("@VarName") 或 替换 XQuery 中的文字值sql:column("ColumnName").

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

这篇关于如何在TSQL中查询特定文本的xml列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！