Store XML format data in SQL Server table

Question

My query is:

Declare @xml1 xml='

<Root>
  <Dir Name="Test">
    <Dir Name="Test1">
      <File>File1<Size>375</Size></File>
      <File>File2<Size>375</Size></File>
      <File>File3<Size>375</Size></File>
      <File>File4<Size>375</Size></File>
      <File>File5<Size>375</Size></File>
      <File>File6<Size>375</Size></File>
    </Dir>
    <Dir Name="Test2" />
  </Dir>
</Root>'

Declare @t table (xmld xml)
Insert into @t values (@xml1)

select * from @t

I want to store the XML data in SQL Server table.

My expected output is:

FolderPath  FileName    FileSize    
--------------------------------
Test        -       -       
Test\Test1      File1       375     
Test\Test1      File2       375     
Test\Test1      File3       375     
Test\Test1      File4       375     
Test\Test1      File5       375
Test\Test2      -       -       

I followed this link for reference but not able to understand.

Can someone help me with this?

Answer 1

I am proposing much better XML structure without namespaces use as a directory name. Even the <File> tag is no so kosher in the original XML.

SQL

DECLARE @xml xml=
N'<Root>
    <Dir name="Test">
        <Dir name="Test1">
            <File name="File1" Size="375"/>
            <File name="File2" Size="375"/>
            <File name="File3" Size="375"/>
        </Dir>
        <Dir name="Test2">
            <File name="File1" Size="575"/>
            <File name="File2" Size="75"/>
            <File name="File3" Size="75"/>
        </Dir>
        <Dir name="Test3"/>
    </Dir>
</Root>';

SELECT dir.c.value('@name','VARCHAR(100)') + '\' +
     subdir.c.value('@name','VARCHAR(100)') AS folderPath
    , fil.c.value('@name','VARCHAR(100)') AS [fileName]
    , fil.c.value('@Size','VARCHAR(100)') AS [fileSize]
FROM @xml.nodes('/Root/Dir') AS dir(c)
    CROSS APPLY dir.c.nodes('Dir') AS subdir(c)
    OUTER APPLY subdir.c.nodes('File') AS fil(c)
ORDER BY folderPath, [fileName];

Output

+------------+----------+----------+
| folderPath | fileName | fileSize |
+------------+----------+----------+
| Test\Test1 | File1    | 375      |
| Test\Test1 | File2    | 375      |
| Test\Test1 | File3    | 375      |
| Test\Test2 | File1    | 575      |
| Test\Test2 | File2    | 75       |
| Test\Test2 | File3    | 75       |
| Test\Test3 | NULL     | NULL     |
+------------+----------+----------+