Reading XML in SQL which has one to many combination

Question

See below example where I am trying to get result as 2 rows having same ClaimNo with different aliasType and aliasName.

Current result looks like this- enter image description here

DECLARE @r TABLE (AliasesValue XML)
INSERT INTO @r 
SELECT '<aliases> <ClaimsNotificationAddRq><Claim><ClaimNo>123</ClaimNo>   <alias>     <aliasType>AKA</aliasType>     <aliasName>Pramod Singh</aliasName>   </alias>   <alias>     <aliasType>AKA</aliasType>     <aliasName>Bijoy Bora</aliasName>   </alias></Claim></ClaimsNotificationAddRq> </aliases> '


SELECT c.query('data(Claim/ClaimNo)'), c.query('data(Claim/alias/aliasType)'), c.query('data(Claim/alias/aliasName)')

FROM @r r CROSS APPLY AliasesValue.nodes('aliases/ClaimsNotificationAddRq') x(c)

Answer 1

Try it like this

DECLARE @r TABLE (AliasesValue XML)
INSERT INTO @r 
SELECT '<aliases>
  <ClaimsNotificationAddRq>
    <Claim>
      <ClaimNo>123</ClaimNo>
      <alias>
        <aliasType>AKA</aliasType>
        <aliasName>Pramod Singh</aliasName>
      </alias>
      <alias>
        <aliasType>AKA</aliasType>
        <aliasName>Bijoy Bora</aliasName>
      </alias>
    </Claim>
  </ClaimsNotificationAddRq>
</aliases>';

SELECT c.value('(ClaimNo)[1]','int') AS ClaimNo
      ,a.value('(aliasType)[1]','nvarchar(max)') AS AliasType
      ,a.value('(aliasName)[1]','nvarchar(max)') AS AliasName
FROM @r r 
CROSS APPLY AliasesValue.nodes('/aliases/ClaimsNotificationAddRq/Claim') x(c)
CROSS APPLY c.nodes('alias') AS y(a)

The result

123 AKA Pramod Singh
123 AKA Bijoy Bora

The first call to .nodes() gets you on the level of one <Claim>. The second call will get all <alias>-nodes below the current <Claim>-element.