title: Read xml data in format :Sql server
body: <p>I have xml data as following:   </p>
  
  <pre><code>&lt;GProposal&gt;
   &lt;UnderwritingMessages&gt;
                              &lt;anyType xsi:type="xsd:string"&gt;3425：入院補償一時金が引受限度額を超えています。引受照会が必要です。&lt;/anyType&gt;
                              &lt;anyType xsi:type="xsd:string"&gt;3428：通院補償一時金が引受限度額を超えています。引受照会が必要です。&lt;/anyType&gt;
                   &lt;/UnderwritingMessages&gt;
      &lt;Plans&gt;
          &lt;Plan&gt;
            &lt;UnderwritingMessages /&gt;
                 &lt;InsuredGroups&gt;
                          &lt;InsuredGroup&gt;
                            &lt;UnderwritingMessages&gt;
                              &lt;anyType xsi:type="xsd:string"&gt;342025：入院補償一時金が引受限度額を超えています。引受照会が必要です。&lt;/anyType&gt;
                              &lt;anyType xsi:type="xsd:string"&gt;342028：通院補償一時金が引受限度額を超えています。引受照会が必要です。&lt;/anyType&gt;
                   &lt;/UnderwritingMessages&gt;
                     &lt;Description&gt;役員・事業主&lt;/Description&gt;
                            &lt;InsuredGroupType&gt;1&lt;/InsuredGroupType&gt;
                  &lt;/InsuredGroup&gt;
               &lt;InsuredGroup&gt;
                        &lt;UnderwritingMessages&gt;
                          &lt;anyType xsi:type="xsd:string"&gt;342025：入院補償一時金が引受限度額を超えています。引受照会が必要です。&lt;/anyType&gt;
                          &lt;anyType xsi:type="xsd:string"&gt;342028：通院補償一時金が引受限度額を超えています。引受照会が必要です。&lt;/anyType&gt;
                        &lt;/UnderwritingMessages&gt;
               &lt;/InsuredGroup&gt;
            &lt;/InsuredGroups&gt;
       &lt;/Plan&gt;
      &lt;/Plans&gt;
  &lt;/GProposal&gt;
  </code></pre>
  
  <p>Insured group can have max of 4 and plans can have max of 3 how do I need to fetch using loop.</p>
  
  <p>I am using the below format to fetch the following values.</p>
  
  <pre><code>select distinct P.proposalid as 'Proposal Id',
       (CASE WHEN p2.XMLData.value('count(/Plans/Plan/InsuredGroups/InsuredGroup/UnderwritingMessages/*)', 'int') &gt; 0 THEN
                  (SELECT TOP 1 T.c.value('.', 'nvarchar(max)') AS result  
                      FROM p2.XMLData.nodes('(/*/Plans/Plan/InsuredGroups/InsuredGroup/UnderwritingMessages)') T(c))
           ELSE
                  (SELECT TOP 1 T.c.value('.', 'nvarchar(max)') AS result  
                      FROM p2.XMLData.nodes('(/*/UnderwritingMessages)') T(c))
      END) as UWReferralComment
  
  from proposal P
  -- referred before
  cross APPLY (
      SELECT TOP 1 ProposalId , Starteffectivedate,Data as XMLData
       FROM Proposal
      WHERE ProposalId = P.ProposalId
      AND ProposalTypeID = 1
      AND ProposalStatusID = 3
      AND EndEffectiveDate &lt;&gt; '21991231'
      --and  len(isnull(Data.value('(/*/Answers/AnswersList/Entry[@key="uc7_UWApprovalNum"]/value)[1]', 'nvarchar(max)'),'') ) &gt; 0
  ) P2
  </code></pre>
  
  <p>Output :
  <a href="https://i.stack.imgur.com/O6p8d.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/O6p8d.jpg" alt="enter image description here"></a>
  I need to combine all the UnderwritingMessages tags to show as string with out duplicates.</p>