sql - MS SQL URL 编码的 XML 读取子节点
问题描述
我需要一些帮助来读取 ms sql 中节点 xml 的值。
桌子看起来像
设置列的数据类型为“xml”
我想用 xml 列中的 sql 查询读出一些信息。
问题是 xml 中的格式，请参见此处：
<FCProfile version="5.1.0.1" fpid="142" fid="5" guid="e24fb72b-fc8f-435b-8b33-562a018d64e5" name="Vertrieb-IF FCO" type="Custom" useIndexFilter="true" fGuid="2ce5d088-509d-4bef-acc2-5d1dd79607a7">
<IndexFilters>
<SearchFilter format="Extended" operation="And">
<ExtendedStatement><?xml version="1.0"?>
<QueryBuilderSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://dev.docuware.com/settings/web/querybuilder">
<Root>
<Operation>And</Operation>
<Conditions xsi:type="Condition">
<DBName>ARCHIV</DBName>
<Provider xsi:type="TextConditionProvider">
<Operation>Equal</Operation>
<Value>Vertrieb</Value>
<SystemFunction>None</SystemFunction>
</Provider>
</Conditions>
<Conditions xsi:type="GroupCondition">
<Operation>Or</Operation>
<Conditions xsi:type="Condition">
<DBName>DOKUMENTART</DBName>
<Provider xsi:type="TextConditionProvider">
<Operation>Equal</Operation>
<Value>Baukasse</Value>
<SystemFunction>None</SystemFunction>
</Provider>
</Conditions>
<Conditions xsi:type="Condition">
<DBName>DOKUMENTART</DBName>
<Provider xsi:type="TextConditionProvider">
<Operation>Equal</Operation>
<Value>Anzahlungsrechnung (ausgehend)</Value>
<SystemFunction>None</SystemFunction>
</Provider>
</Conditions>
<Conditions xsi:type="Condition">
<DBName>DOKUMENTART</DBName>
<Provider xsi:type="TextConditionProvider">
<Operation>Equal</Operation>
<Value>Sonderrechnung</Value>
<SystemFunction>None</SystemFunction>
</Provider>
</Conditions>
</Conditions>
</Root>
</QueryBuilderSettings></ExtendedStatement>
</SearchFilter>
</IndexFilters>
<GeneralRights>General_Search, General_DisplayDocument, General_Export</GeneralRights>
<AnnotationRights>Annotation_New</AnnotationRights>
<FieldRights>
<FieldRight right="Field_Modify" />
<FieldRight right="Field_Read">
<Fields>,KUNDE,AUFTRAG,MASCHINEN,DOKUMENTART,DOKUMENTDATUM,BETREFF,BEREICH,VORGANG,STATUS,DATEINAME,GESELLSCHAFT,HANDELSPARTNER,HANDELSPARTNERNR,PROJEKT,DOKUMENT_NR,ENDKUNDE,NAME_KONTAKT,BETRAG,ABTEILUNG,VERTRETER,ZEITRAUM_VON,ZEITRAUM_BIS,ARCHIV,ABGELEGT_VON,</Fields>
</FieldRight>
<FieldRight right="Field_Search">
<Fields>,KUNDE,AUFTRAG,MASCHINEN,DOKUMENTART,DOKUMENTDATUM,BETREFF,BEREICH,VORGANG,STATUS,DATEINAME,GESELLSCHAFT,HANDELSPARTNER,HANDELSPARTNERNR,PROJEKT,DOKUMENT_NR,ENDKUNDE,NAME_KONTAKT,BETRAG,ABTEILUNG,VERTRETER,ZEITRAUM_VON,ZEITRAUM_BIS,ARCHIV,ABGELEGT_VON,</Fields>
</FieldRight>
<FieldRight right="Field_Write" />
</FieldRights>
</FCProfile>
有些部分用“<”和“>”括起来，有些用“& lt;”括起来 和“>”
我试过的脚本是：
select X.Y.value('(ExtendedStatement)[1]', 'VARCHAR(4000)') as SettingsText
from [dwsystem].[dbo].[DWFCProfile] e
OUTER APPLY e.settings.nodes('FCProfile/IndexFilters/SearchFilter') as X(Y)
where fid=5
但我只想读出“＆lt;”中的“价值” 值“>” 部分喜欢：Vertrieb，或 Baukasse，或 Anzahlungsrechnung
如果我尝试这样的事情：
select X.Y.value('(Value)[1]', 'VARCHAR(4000)') as SettingsText
from [dwsystem].[dbo].[DWFCProfile] e
OUTER APPLY e.settings.nodes('FCProfile/IndexFilters/SearchFilter/ExtendedStatement') as X(Y)
where fid=5
我得到一个“NULL”值。
我也试过这个：
select cast(settings.value('(ExtendedStatement)[1]', 'varchar(max)') as xml).value('(FCProfile/IndexFilters/SearchFilter/ExtendedStatement/value)[1]', 'varchar(max)') as Text
FROM [dwsystem].[dbo].[DWFCProfile]
where fid=5
我也得到一个“NULL”值。
我发现了一个有趣的博客来解决我的问题：
https://mcguirev10.com/2018/12/16/query-plaintext-or-encoded-xml-with-sql-server.html
但我无法得到解决方案。
在这里你可以看到我的数据库中的数据：
如果有人可以帮助我会很棒:)
//编辑
现在我尝试了答案：（通过列操作修改它）
with xmlnamespaces (
'http://dev.docuware.com/settings/web/querybuilder' as qb
)
select
a.name as Benutzer,
c.name as Gruppe,
e.name as Rolle,
i.name as Archivprofile,
k.name as Archiv,
Provider.value('(qb:Value/text())[1]', 'nvarchar(max)') as Value,
Provider.value('(qb:Operation/text())[1]', 'nvarchar(max)') as Operation
from dbo.DWUser as a
left join dbo.DWUserToGroup b on b.uid=a.uid
left join dbo.DWGroup c on c.gid=b.gid
left join dbo.DWUserToRole d on d.uid=a.uid
left join dbo.DWGroupToRole j on j.gid=c.gid
left join dbo.DWRoles e on e.rid=j.rid
left join dbo.DWFCProfileToRole h on h.rid=e.rid
left join dbo.DWFCProfile i on i.fpid=h.fpid
left join dbo.DWFileCabinet k on k.fid=i.fid
outer apply i.settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') SearchFilter(ExtendedStatement)
outer apply (
select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
) Embedded
outer apply EmbeddedXML.nodes('/qb:QueryBuilderSettings/qb:Root/qb:Conditions/qb:Provider') as Conditions(Provider)
order by k.name;
此解决方案仅返回 XML 节点“值”的第一个输入值
是否可以从数据中获取“值”的所有输入？
就像在 XML 中一样，您有 fpid = 142 和 fid = 5，“值”有 4 个值（Vertrieb、Baukasse、Anzahlungsrechnung 和 Sonderrechnung）。
& lt;Value& gt;Vertrieb& lt;/Value& gt;
& lt;Value& gt;Baukasse& lt;/Value& gt;
& lt;Value& gt;Anzahlungsrechnung (ausgehend)& lt;/Value& gt;
& lt;Value& gt;Sonderrechnung & lt;/Value& gt;
这是数据库的屏幕截图
在示例中，选择应该给出
4 次 fpid = 142 和 fid = 5 值类似（见上文）可能在 fpid = 26 和 fid = 5 的 xml 列设置中，在 fpid = 177 和 fid = 5 的“值”中有 2 个值，fid = 5 只有一个xml 列中“值”中的值
等等。
解决方案
好吧，您在这里似乎有两个问题：
- 嵌入式 XML，以及
- 带有命名空间的嵌入式 XML。
您尝试在嵌入式中查询的元素在QueryBuilder元素 of上定义了默认命名空间http://dev.docuware.com/settings/web/querybuilder，因此您需要在 XPath 查询中引用它...
with xmlnamespaces (
'http://dev.docuware.com/settings/web/querybuilder' as qb
)
select Provider.value('(qb:Value/text())[1]', 'nvarchar(max)') as Value
from (
select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
from dbo.DWFCProfile
cross apply settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') SearchFilter(ExtendedStatement)
where fid = 5
) Embedded
cross apply EmbeddedXML.nodes('/qb:QueryBuilderSettings/qb:Root/qb:Conditions/qb:Provider') as Conditions(Provider);
因为left join您的扩展查询中涉及的 s 太多，所以即使某些值可能为空，您也可以使用outer apply而不是cross apply允许返回行。所以看起来如下（未经测试，因为我没有你的数据库可用）......
with xmlnamespaces (
'http://dev.docuware.com/settings/web/querybuilder' as qb
)
select
a.name as Benutzer,
c.name as Gruppe,
e.name as Rolle,
i.name as Archivprofile,
k.name as Archiv,
Provider.value('(qb:Value/text())[1]', 'nvarchar(max)') as Value
from dbo.DWUser as a
left join dbo.DWUserToGroup b on b.uid=a.uid
left join dbo.DWGroup c on c.gid=b.gid
left join dbo.DWUserToRole d on d.uid=a.uid
left join dbo.DWGroupToRole j on j.gid=c.gid
left join dbo.DWRoles e on e.rid=j.rid
left join dbo.DWFCProfileToRole h on h.rid=e.rid
left join dbo.DWFCProfile i on i.fpid=h.fpid
left join dbo.DWFileCabinet k on k.fid=i.fid
outer apply i.settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') SearchFilter(ExtendedStatement)
outer apply (
select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
) Embedded
outer apply EmbeddedXML.nodes('/qb:QueryBuilderSettings/qb:Root/qb:Conditions/qb:Provider') as Conditions(Provider)
order by k.name;
推荐阅读
- python - 通过 Ctypes 在 Python 中的 C++ 类
- php - Laravel DOMPDF 超出内存限制
- typescript - @typescript-eslint/no-misused-promises 错误与 Firebase signInWithEmailAndPassword 方法调用
- python - 如何使用包含 ESP32 I2C 的 Python 对 Grove - I2C 高精度温度传感器 (MCP9808) 进行编程？
- python - 如何从 Python Pandas 的列表中捕获数据帧？
- python - 将字符串编码的列表转换为新的数据帧
- java - Java 中带有 Document 和 BasicDBObject 的通用方法
- flutter - 接收列表的两个不同的随机元素而不删除 | 颤振/飞镖
- javascript - 使用条件减少 mongodb 聚合
- oracle - PL/SQL 表函数之间的流水线