从sql server的值中选择nvarchar(max)xml

Question

我有以下XML:

<Attributes>
<Map>
<entry key="approvalSet">
<value>
<Approval>
<ApprovalItem application="SP" id="9659102e" name="reporting_queue" operation="Add" />
<ApprovalItem application="SP" id="1013b3f5" name="reporting_queue" operation="Add" />
<ApprovalItem application="SP" id="09648f81" name="reporting_queue" operation="Add" />
</Approval>
</value>
</entry>
<entry key="DisplayName" value=" mike " />
<entry key="Name" value="1222" />
<entry key="policy" />
<entry key="work">
<value>
<WorkLevel>Normal</WorkLevel>
</value>
</entry>
</Map>
</Attributes>

我想运行一个select sql，其中一列(属性(具有xml格式(附在此处(。sql类似于：

SELECT 
w.created,
W.[attributes],
W.[completer]
FROM [item_archive] W 
WHERE W.type='Manual'

如何从具有XML格式的attributes列中分别获得新列(application、id、name和operation(值的记录值的结果。XML列类型为nvarchar((max),null)。

Answer 1

像这样：

drop table if exists #item_archive 
create table #item_archive
( 
created datetime,
attributes varchar(max) null,
completer varchar(20),
type varchar(20)
)
declare @d varchar(max) = '
<Attributes>
<Map>
<entry key="approvalSet">
<value>
<Approval>
<ApprovalItem application="SP" id="9659102e" name="reporting_queue" operation="Add" />
<ApprovalItem application="SP" id="1013b3f5" name="reporting_queue" operation="Add" />
<ApprovalItem application="SP" id="09648f81" name="reporting_queue" operation="Add" />
</Approval>
</value>
</entry>
<entry key="DisplayName" value=" mike " />
<entry key="Name" value="1222" />
<entry key="policy" />
<entry key="work">
<value>
<WorkLevel>Normal</WorkLevel>
</value>
</entry>
</Map>
</Attributes>'
insert into #item_archive(created,attributes,completer,type)
values (getdate(),@d,'joe','Manual')
go
with W as 
(
select *, cast(attributes as xml) attributes_xml
from #item_archive
)
select 
w.created,
w.completer,
A.ApprovalItem.value('@application','varchar(20)') application,
A.ApprovalItem.value('@id','varchar(20)') id,
A.ApprovalItem.value('@name','varchar(50)') name,
A.ApprovalItem.value('@operation','varchar(20)') operation
from  W
cross apply W.attributes_xml.nodes('/Attributes/Map/entry[@key="approvalSet"]/value/Approval/ApprovalItem') A(approvalItem)
where W.type = 'Manual'

输出

created                 completer            application          id                   name                                               operation
----------------------- -------------------- -------------------- -------------------- -------------------------------------------------- --------------------
2018-12-04 16:41:03.550 joe                  SP                   9659102e             reporting_queue                                    Add
2018-12-04 16:41:03.550 joe                  SP                   1013b3f5             reporting_queue                                    Add
2018-12-04 16:41:03.550 joe                  SP                   09648f81             reporting_queue                                    Add
(3 rows affected)