CODEWITHSUNDEEP
sql-serverxmlsql-server-2008
user2687103
user2687103

Reputation: 51

Query XML column in SQL SERVER

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?

Upvotes: 3

Views: 127

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 2

user2687103
user2687103

Reputation: 51

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'

Upvotes: 1

marc_s
marc_s

Reputation: 755321

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

Upvotes: 2

Related Questions