CODEWITHSUNDEEP
sql-serverxmlxquery-sql
user1893874
user1893874

Reputation: 843

If condition on Selecting XML node in SQL

I am trying to read the xml and storing it in SQL server.

DECLARE @xml XML
SET @xml = 
 '<report>
    <personal>
       <search>
           <subject>
               <name>SearchName</name>
           </subject>
       </search>    
    </personal>
    <personal>
       <search>
           <subject>
               <name>SearchName</name>
           </subject>
       </search>
       <result>
           <history>
              <name>HistoryName</name>
           </history>
       </result>
    </personal>
  </report>
'

What i am trying here is - selecting the name but condition here is if <personal> contains <result> then select the name under history/name

if <personal> doesn't contain <result> select the name under subject/name

currently i am selecting names from personal/subject as below:

Select 
A.Search.value('(subject/name)[1]','varchar(max)')
FROM  @xml.nodes('/report/personal/search') as A(Search)

Expecting result:

SearchName
HistoryName

How to add condition in between?

Is there any way we can add exists condition here

 SELECT @xml.exist('//report//personal//search//subject//name')

Upvotes: 0

Views: 281

Answers (2)

jpw
jpw

Reputation: 44921

This:

SELECT 
    COALESCE(
       A.Search.value('(result/history/name)[1]','varchar(max)'),
       A.Search.value('(search/subject/name)[1]','varchar(max)')
    ) AS Name
FROM @xml.nodes('/report/personal') as A(Search)

will return:

Name
------------
SearchName
HistoryName

Upvotes: 0

Vado
Vado

Reputation: 134

Select coalesce(A.Search.value('(result/history/name)[1]', 'varchar(max)'), A.Search.value('(search/subject/name)[1]','varchar(max)'))
FROM @xml.nodes('/report/personal') as A(Search)

Upvotes: 1

Related Questions