在SQL SERVER中查询XML列

I have following XML in table ALL_MRN column PID3:

我在表ALL_MRN列PID3中有以下XML:

<Repetitions>
 <Repetition>
  <Field>10228^^^L8 MRN^MRN</Field>
  <Component_1>10228</Component_1>
  <Component_2>L8 MRN</Component_2>
  <Component_3>MRN</Component_3>
 </Repetition>
 <Repetition>
  <Field>00283^^^CMRN^CMRN</Field>
  <Component_1>00283</Component_1>
  <Component_2>CMRN</Component_2>
  <Component_3>CMRN</Component_3>
 </Repetition>
 <Repetition>
  <Field>00294^^^L7 MRN^MRN</Field>
  <Component_1>00283</Component_1>
  <Component_2>L7 MRN</Component_2>
  <Component_3>MRN</Component_3>
 </Repetition>
</Repetitions>

I am trying to find the CMRN value in a Component_3 tag and if found then concatenate the values from Component_1 and Component_3 to form a string.

我试图在Component_3标记中找到CMRN值,如果找到,则连接Component_1和Component_3中的值以形成字符串。

Is it possible without using cursors or loops? What is the best way to get this concatenated string?

是否可以不使用游标或循环?获得这个串联字符串的最佳方法是什么?

3 个解决方案

#1

How about this:

这个怎么样:

SELECT
    C1_and_C3 = XRep.value('(Component_1)[1]', 'varchar(50)') + XRep.value('(Component_3)[1]', 'varchar(50)')
FROM 
    dbo.ALL_MRN
CROSS APPLY 
    PID3.nodes('/Repetitions/Repetition') AS XTbl(XRep)
WHERE
    XRep.value('(Component_3)[1]', 'varchar(50)') = 'CMRN'

SQL Server 2005 and newer have pretty great XQuery support - there's really no need for cursors!

SQL Server 2005和更新版本有非常好的XQuery支持 - 实际上不需要游标!

BTW: your XML is invalid - you cannot have a leading <Component_3> tag and close that with a </Component_5> (same goes for <Component_2>....</Component_4> )

顺便说一句:您的XML无效 - 您不能拥有前导标签并使用关闭(同样适用于 .... )

#2

Another option, which uses the XQuery language and exist method. If the exist method return a 1(True), it indicates that Component_3 tag includes the CMRN value

另一个选项,它使用XQuery语言和exists方法。如果exists方法返回1(True),则表示Component_3标记包含CMRN值

DECLARE @xml xml =
'<Repetitions>
 <Repetition>
  <Field>10228^^^L8 MRN^MRN</Field>
  <Component_1>10228</Component_1>
  <Component_2>L8 MRN</Component_2>
  <Component_3>MRN</Component_3>
 </Repetition>
<Repetition>
 <Field>00283^^^CMRN^CMRN</Field>
 <Component_1>00283</Component_1>
 <Component_2>CMRN</Component_2>
 <Component_3>CMRN</Component_3>
</Repetition>
<Repetition>
 <Field>00294^^^L7 MRN^MRN</Field>
 <Component_1>00283</Component_1>
 <Component_2>L7 MRN</Component_2>
 <Component_3>MRN</Component_3>
 </Repetition>
</Repetitions>'

DECLARE @Component_3 varchar(50) = 'CMRN'
SELECT xmlCol.value('Component_1[1]', 'varchar(50)') + xmlCol.value('Component_3[1]', 'varchar(50)') 
FROM @xml.nodes('//Repetitions/Repetition') xmlTab(xmlCol)
WHERE xmlCol.exist('Component_3[text() = sql:variable("@Component_3")]') = 1

See demo on SQLFiddle

请参阅SQLFiddle上的演示

#3

It worked. PID3 was varchar type so I had to cast to XML. Here is the updated version.

有效。 PID3是varchar类型所以我不得不转换为XML。这是更新版本。

SELECT
  C1_and_C3 = XRep.value('(Component_1)[1]', 'varchar(50)') + XRep.value('(Component_3)[1]', 'varchar(50)')
FROM 
  dbo.ALL_MRN
CROSS APPLY 
  (select cast(PID3 as XML) ) as t1(x)
CROSS APPLY 
  x.nodes('/Repetitions/Repetition') AS XTbl(XRep)
WHERE
  XRep.value('(Component_3)[1]', 'varchar(50)') = 'CMRN'

#1