从NTEXT分解SQL Server中的XML

Question

希望这对专家来说很简单:)

我有一个包含XML的列(在这篇文章的底部)-该列是ntext数据类型。我不想在一列中显示整个XML字符串，而是希望将其分解为多个列。数据库类型为SQL Server 2008 R2。不能更改列数据类型(MS应用程序数据库)

我尝试了xpath查询的多种变体，但我一直得到空值。希望是我引用它的方式不对。任何帮助都将非常感激，因为我似乎无法理解XML(不是因为缺乏尝试!):

XML(示例来自结果中的一条记录-为了便于阅读，我将其分开):

<?xml version="1.0" encoding="utf-16"?>  
<q1:ErrorInfo ErrorCode="1073744938" DetailedCode="0" DetailedSource="0" ExceptionDetails="" xmlns:q1="http://schemas.microsoft.com/2003/dls/GenericAgentStatus.xsd">    
<q1:Parameter Name="datasourceid" Value="48c3db91-4ba9-46a5-820b-a2ab2c0733aa" />    
<q1:Parameter Name="doesalertneedstroubleshootuiformoredetails" Value="False" />    
<q1:Parameter Name="failurecount" Value="1" />    
<q1:Parameter Name="customparameterformom" Value="86924" />    
<q1:Parameter Name="datasourcename" Value="servernamedatabase1" />    
<q1:Parameter Name="protectedgroup" Value="ProtectionGroup1 Servers - servername.domain" />    
<q1:Parameter Name="servername" Value="servername.domain" />  
</q1:ErrorInfo>

我正在使用的SQL(返回空值):

WITH XMLNAMESPACES('http://schemas.microsoft.com/2003/dls/GenericAgentStatus.xsd' as q1)                        
select cast(ErrorXml as XML).value('(/q1:ErrorInfo/q1:Parameter[6]/q1:value[0]/text())[1]','varchar(MAX)') as [servername]
from
target_table

在所有这些结束时，我想拆分服务器名，保护组，数据源名。

如果你能指出我哪里做错了，我将不胜感激。

Thanks in advance

Answer 1

很不幸的是，似乎我不能把评论标记为答案-看了一下SO论坛，这是故意的。感谢这篇文章的两位评论者。

我使用的解决方案来自har07，但我确实尝试了Marcus的解决方案，效果也很好。

我的重写代码现在看起来像这样…并且工作完美:

    select cast(ls.ErrorXml as XML).value('(/q1:ErrorInfo/q1:Parameter[6]/@Value)[1]','varchar(MAX)') as [ServerName],
cast(ls.ErrorXml as XML).value('(/q1:ErrorInfo/q1:Parameter[5]/@Value)[1]','varchar(MAX)') as [ProtectedGroup],
cast(ls.ErrorXml as XML).value('(/q1:ErrorInfo/q1:Parameter[4]/@Value)[1]','varchar(MAX)') as [DataSourceName],

…

再次感谢-节省了我这么多的时间和理智!

Answer 2

一般来说，在XPath中您使用@attributeName来选择XML属性，您可以尝试这样做:

/q1:ErrorInfo/q1:Parameter[6]/@Value