If condition on Selecting XML node in SQL

Question

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

Vado · Answer 1 · 2015-07-24T00:29:45.177

1

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)

edited Jul 24 '15 at 00:29

answered Jul 24 '15 at 00:08

Vado

134
7

It returns SearchName but i want to get both SearchName and HistoryName in a single query – user1893874 Jul 24 '15 at 00:21
Yes, that is what i am looking for. Thank you :) – user1893874 Jul 24 '15 at 00:38

score 0 · Accepted Answer · answered Jul 24 '15 at 00:29

0

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

answered Jul 24 '15 at 00:29

jpw

44,361
6
66
86

@user1893874 Happy to help :) – jpw Jul 24 '15 at 00:35

If condition on Selecting XML node in SQL

2 Answers2