SQL Server解析XML命名空间

Question

我试图在SQL Server中解析下面的XML，并处理下面的节点：

<PrebillMarkup xmlns="clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">  
<PrebillMarkup.NewValues>   
<x:String x:Key="Narrative">Calls with P.</x:String>
</PrebillMarkup.NewValues> 
<PrebillMarkup.OriginalValues>   
<x:String x:Key="Narrative">Calls with P on confi agreement.</x:String>   
</PrebillMarkup.OriginalValues>
</PrebillMarkup>

我还没有从所有的研究中得到任何SQL，因为我似乎找不到任何接近我试图解析的名称空间。预期输出为：

Original Values | New Values
_________________________________________________
Call with P     | Calls with P on confi agreement

Answer 1

它与@DavidBrowne Microsoft解决方案非常相似，只是性能优化。

SQL

DECLARE @xml XML = 
N'<PrebillMarkup xmlns="clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
<PrebillMarkup.NewValues>
<x:String x:Key="Narrative">Calls with P.</x:String>
</PrebillMarkup.NewValues>
<PrebillMarkup.OriginalValues>
<x:String x:Key="Narrative">Calls with P on confi agreement.</x:String>
</PrebillMarkup.OriginalValues>
</PrebillMarkup>';
WITH XMLNAMESPACES(DEFAULT 'clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query'
, 'http://schemas.microsoft.com/winfx/2006/xaml' AS x)
SELECT c.value('(PrebillMarkup.NewValues/x:String/text())[1]', 'VARCHAR(100)') AS [Original Values]
, c.value('(PrebillMarkup.OriginalValues/x:String/text())[1]', 'VARCHAR(100)') AS [New Values]
FROM @xml.nodes('/PrebillMarkup') AS t(c);

输出

+-----------------+----------------------------------+
| Original Values |            New Values            |
+-----------------+----------------------------------+
| Calls with P.   | Calls with P on confi agreement. |
+-----------------+----------------------------------+

Answer 2

您需要对查询进行with xmlnamespaces声明。例如

declare @doc xml = '
<PrebillMarkup xmlns="clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">  
<PrebillMarkup.NewValues>   
<x:String x:Key="Narrative">Calls with P.</x:String>
</PrebillMarkup.NewValues> 
<PrebillMarkup.OriginalValues>   
<x:String x:Key="Narrative">Calls with P on confi agreement.</x:String>   
</PrebillMarkup.OriginalValues>
</PrebillMarkup>
';
with xmlnamespaces ('http://schemas.microsoft.com/winfx/2006/xaml' as x,
default 'clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query')
select @doc.value('(/PrebillMarkup/PrebillMarkup.OriginalValues/x:String)[1]','nvarchar(50)') [Original Values],
@doc.value('(/PrebillMarkup/PrebillMarkup.NewValues/x:String)[1]','nvarchar(50)') [New Values]

输出

Original Values                                    New Values
-------------------------------------------------- --------------------------------------------------
Calls with P on confi agreement.                   Calls with P.
(1 row affected)

按照惯例，您可以将xmlnamespace别名与xml文档中使用的名称空间别名相匹配，但从技术上讲，这不是必需的。这也起作用：

with xmlnamespaces ('http://schemas.microsoft.com/winfx/2006/xaml' as foo,
'clr-namespace:Xyz.Query.ViewModels;assembly=Xyz.Query' as bar)
select @doc.value('(/bar:PrebillMarkup/bar:PrebillMarkup.OriginalValues/foo:String)[1]','nvarchar(50)') [Original Values],
@doc.value('(/bar:PrebillMarkup/bar:PrebillMarkup.NewValues/foo:String)[1]','nvarchar(50)') [New Values]