时间戳

首页 > 解决方案 > 从 MSSQL 中的 XML 列中提取 xml 标记数据

sql - 从 MSSQL 中的 XML 列中提取 xml 标记数据

问题描述

就像我之前的问题一样，我想通过 sql 查询提取所有“名称”和路径标签（特权用户 --> 条目 --> 条目 --> 名称）。谢谢

<Policy>
 <id>f3d25685-fb54-438d-a0e9-03e4ba5b555f</id>
 <name>SG</name>
 <description />
 <attributes>0</attributes>
 <parentPolicy>a7c603c0-8700-45ef-9165-c0134e292ee4</parentPolicy>
 <sharedPasswordRev>-1</sharedPasswordRev>
 <useShortChallengeResponse>false</useShortChallengeResponse>
 <settings>
 <privilegedUsers>
 <entries>
  <entry>
   <name>CEO</name>
   <path>CN=CEO,CN=Users,DC=sglab,DC=local</path>
   <uid>27C9C6AC9311424BB332CC5AB04B047D</uid>
   <serverName>192.168.7.201</serverName>
   <sid>S-1-5-21-3362085216-3357124804-2073486349-1108</sid>
   <type>user</type>
 </entry>
 <entry>
    <name>Administrator</name>
    <path>CN=Administrator,CN=Users,DC=sglab,DC=local</path>
    <uid>EBBC4D71CD68BE4BA49BC8B6DF01F954</uid>
    <serverName>192.168.7.201</serverName>
    <sid>S-1-5-21-3362085216-3357124804-2073486349-500</sid>
    <type>user</type>
   </entry>
 </privilegedUsers>
 <unknownAppsStrategy>EDITOR</unknownAppsStrategy>
</settings>
</Policy>

标签： sqlsql-serverxml

解决方案

您的 xml 格式不正确。你没有结束</entries>标签。

当我修复它时，这是您的代码：

declare @xml xml
set @xml=convert(xml,'<Policy>
 <id>f3d25685-fb54-438d-a0e9-03e4ba5b555f</id>
 <name>SG</name>
 <description />
 <attributes>0</attributes>
 <parentPolicy>a7c603c0-8700-45ef-9165-c0134e292ee4</parentPolicy>
 <sharedPasswordRev>-1</sharedPasswordRev>
 <useShortChallengeResponse>false</useShortChallengeResponse>
 <settings>
 <privilegedUsers>
 <entries>
  <entry>
   <name>CEO</name>
   <path>CN=CEO,CN=Users,DC=sglab,DC=local</path>
   <uid>27C9C6AC9311424BB332CC5AB04B047D</uid>
   <serverName>192.168.7.201</serverName>
   <sid>S-1-5-21-3362085216-3357124804-2073486349-1108</sid>
   <type>user</type>
 </entry>
 <entry>
    <name>Administrator</name>
    <path>CN=Administrator,CN=Users,DC=sglab,DC=local</path>
    <uid>EBBC4D71CD68BE4BA49BC8B6DF01F954</uid>
    <serverName>192.168.7.201</serverName>
    <sid>S-1-5-21-3362085216-3357124804-2073486349-500</sid>
    <type>user</type>
   </entry>
   </entries>
 </privilegedUsers>
 <unknownAppsStrategy>EDITOR</unknownAppsStrategy>
</settings>
</Policy>')

select @xml.query('/Policy/settings/privilegedUsers/entries/entry/name')

推荐阅读