Reading XML into SQL tables

Question

I am reading the xml and writing it into sql tables

Want to insert below xml into table

Declare @MainXml XML =
'<Parent>
  <Root>
      <Admin>admin1</Admin>
      <Data number="1">
        <Type value="100"></Type>
        <Type value="200"></Type>
     </Data>        
  </Root>
 <Root>
     <Admin>admin2</Admin>
     <Data number="1">
       <Type value="300"></Type>
       <Type value="400"></Type>
     </Data>
 </Root>
</Parent>'

I am trying to insert into table like this

Insert Into Table1(col1,col2)
  SELECT
   A.RT.value('Admin[1]','varchar(max)'),
   B.DT.value('@value','varchar(max)')
   FROM @MainXml.nodes('Parent/Root') as A(RT)

cross apply A.RT.nodes('Data/Type') as B(DT)

It will work fine on above xml.

If section is removed it wont work.

Why my query is not work for

Declare @MainXml XML =
'<Parent>
  <Root>
      <Admin>admin1</Admin>     
  </Root>
 <Root>
     <Admin>admin2</Admin>
 </Root>
</Parent>'

Section in my XML may or may not be there. so how to handle such xml.

If no data, want to enter null to it.

Please suggest me.

Answer 1

This should work:

EDIT: Oh I just realised, that there are more than one Type-nodes. Wait a little...

OK, you did perfectly well, just change CROSS APPLY to OUTER APPLY...

HTH