CODEWITHSUNDEEP
sql-serverxml
user2561997
user2561997

Reputation: 443

XML Query within Multiple Rows of Data

In my database table I have a XML column.

The value in the XML column is something like this:

Row1:

  <Data>
  <COLRowData>
  <DORowData>
  <PrimaryValue>Fever</PrimaryValue>  
  <EMRValue1> 101 <EMRValue1 />   
  <DataRowType>Past</DataRowType>
  <HasPositiveValue>True</HasPositiveValue>
  </DORowData>
  <DORowData>
  <PrimaryValue>Hypertension</PrimaryValue>
  <DataRowType>Present</DataRowType>
  <EMRValue1> No </EMRValue1>  
  <HasPositiveValue>False</HasPositiveValue>
 </DORowData>
 </COLRowData>
 </Data>

Row2:

 <Data>
 <COLRowData>
 <DORowData>
  <PrimaryValue>Diabetes</PrimaryValue>
  <DataRowType>Past</DataRowType>    
  <EMRValue1> No </EMRValue1>  
  <HasPositiveValue>True</HasPositiveValue>   
 </DORowData>
 <DORowData>
<PrimaryValue>Fever</PrimaryValue>
  <DataRowType>Present</DataRowType>
  <EMRValue1> 103  <EMRValue1 />
  <HasPositiveValue>True</HasPositiveValue>    
 </DORowData>    
 </COLRowData>
 </Data>

From this table I want to find all such rows where this following combination of Data exists in the single XML column:

I am using this query:

Select * From TABLE 
WHERE  
   XML.exist('/Data/COLRowData/DORowData/DataRowType/text()[. = "Present"]') = 1 
   and
     XML.exist('/Data/COLRowData/DORowData/PrimaryValue/text()[. = "Fever"]') = 1

This query returns both the rows where as my requirement is that I should only find the Row2 as per the combination mentioned above.

Please help me with the query

Also how can i include the EMRValue1 - Value in Select statement from the XML satisfying the where clause?

Thanks i-one. But since my where clause is satisfying data from this block only

   <DORowData>
    <PrimaryValue>Fever</PrimaryValue>
    <DataRowType>Present</DataRowType>
    <EMRValue1> 103  <EMRValue1 />
     <HasPositiveValue>True</HasPositiveValue>    
    </DORowData>

The EMRValue1 in the Select clause should only return 1 row with the EMRValue1 = 103

Upvotes: 0

Views: 1228

Answers (1)

i-one
i-one

Reputation: 5120

You should combine both conditions inside the same XPath:

select * from [TABLE]
where
    XML.exist('/Data/COLRowData/DORowData[DataRowType/text()[. = "Present"] and PrimaryValue/text()[. = "Fever"]]') = 1

To include EMRValue1 in the select list:

select d.*, T.c.value('text()[1]', 'varchar(200)') EMRValue1
from [TABLE] d
    cross apply d.XML.nodes('/Data/COLRowData/DORowData/EMRValue1') T(c)
where d.XML.exist(...) = 1

note, that number of rows returned will inrease, because of multiple EMRValue1 elements in XML data for each row

Upvotes: 1

Related Questions