Query XML column in SQL SERVER

Question

I have following XML in table ALL_MRN column PID3:

<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.

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

You have closing tag </Component_5> for opening tag <Component_3> and 4 for 2. — i-one
– i-one, Commented Aug 15, 2013 at 19:41

marc_s · Accepted Answer · 2013-08-15 19:40:24Z

2

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!

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> )

answered Aug 15, 2013 at 19:40

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oleksandr Fedorenko · Accepted Answer · 2013-08-15 22:38:37Z

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

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

user2687103 · Accepted Answer · 2013-08-15 21:23:25Z

1

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

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'

answered Aug 15, 2013 at 21:23

user2687103

516 bronze badges

Collectives™ on Stack Overflow

Query XML column in SQL SERVER

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related