如何在TSQL中查询XML类型列InstanceSecurity中的指定文本Simple？

Hey there! Let's figure out how to pull the text "Simple" from your InstanceSecurity XML column. I'm assuming you're using SQL Server here (since that's the most common database system with native xml-type columns)—here's what you need to do:

Basic Query (Single Match) #

Use the value() method with an XPath expression to target the Mode attribute of the Authorization element directly:

SELECT
    -- The XPath targets the Mode attribute, [1] ensures we grab the first matching element
    InstanceSecurity.value('(/InstanceSecurity/Folder/Authorization/@Mode)[1]', 'varchar(50)') AS AuthorizationMode
FROM
    YourTableName; -- Replace this with your actual table name

If Multiple Authorization Elements Exist #

If your XML might have multiple Authorization nodes under Folder, use nodes() to shred the XML and get all Mode values:

SELECT
    AuthNode.value('@Mode', 'varchar(50)') AS AuthorizationMode
FROM
    YourTableName -- Replace with your table name
CROSS APPLY
    InstanceSecurity.nodes('/InstanceSecurity/Folder/Authorization') AS N(AuthNode);

Quick Breakdown #

• The XPath /InstanceSecurity/Folder/Authorization/@Mode navigates straight to the Mode attribute nested in your XML structure.
• The value() method converts the XML attribute value to a standard SQL data type (here varchar(50)—feel free to adjust the length if needed).

内容的提问来源于stack exchange，提问作者Christopher Didamo DCSO