使用where子句SQL Server选择XML元素

Question

我在Server中有一个XML存储，其结构如下：

<ecHeaderData>
    <FirstVersionSource>System1</FirstVersionSource>
    <DMSEntryUserID>jsmith</DMSEntryUserID>
</ecHeaderData>

我想返回DMSEntryUserID不为空的所有行。我希望在查询中同时选择FirstVersionSource和DMSEntryuserID。

就像这样：

select deal_jacket_xml('/ecHeader/FirstVersionSource') as FirstVersionSource, 
deal_jacket_xml('/ecHeader/DMSEntryUserID') as DMSEntryUserID
from deal_jacket_xml
where deal_jacket_xml('NotBlank(/ecHeader/DMSEntryUserID'))=1
order by [deal_jacket_xml_id] desc

Answer 1

这是我想出来的。关键是1的括号和索引规范。

select distinct deal_jacket_xml.value('(/eContractingData/ecHeaderData/FirstVersionSource)[1]', 'nvarchar(max)') as FirstVersionSource
from deal_jacket_xml
where deal_jacket_xml.value('(/eContractingData/ecHeaderData/FirstVersionSource)[1]', 'nvarchar(max)') is not null
AND deal_jacket_xml.value('(/eContractingData/ecHeaderData/DMSEntryUserID)[1]', 'nvarchar(max)') is not null