Parse nested xml using sql server

Question

I would like to parse XML using SQL query. Below is the query and XML. Please find below current result and expected result screen shots. Its taking only one value from nested nodes. Please suggest

DECLARE @xml XML =
'<FileGroups xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FileGroup>
  <FileName>Test1</FileName>    
        <Files>
             <File>
      <FilePath>\\Server\Test1</FilePath>
      <FileGUID>3006989A-725E-40E8-BAF7-A094CB710AC3</FileGUID>      
      <DependentOnFileNames></DependentOnFileNames>    
   </File>
   <File>
    <FilePath>\\Server\Test2</FilePath>
    <FileGUID>A584CE87-CC76-484E-ACE4-53C6CAD27B7F</FileGUID>    
    <DependentOnFileNames></DependentOnFileNames>
   </File>
             <File>
    <FilePath>\\Server\Test3</FilePath>
    <FileGUID>727A6BBE-E820-4431-9958-93D0863F65B8</FileGUID>
    <Comments></Comments>
    <DependentOnFileNames>
     <FileGUID>3006989A-725E-40E8-BAF7-A094CB710AC3</FileGUID>
     <FileGUID>A584CE87-CC76-484E-ACE4-53C6CAD27B7F</FileGUID>
    </DependentOnFileNames>
   </File>
         </Files>
     </FileGroup>
  <FileGroup>
  <FileName>Test2</FileName>    
        <Files>
             <File>
      <FilePath>\\Server\Test4</FilePath>
      <FileGUID>EA422762-58CD-423D-92D4-1DC18A312F48</FileGUID>      
      <DependentOnFileNames></DependentOnFileNames>    
   </File>   
         </Files>  
   </FileGroup>  
</FileGroups>'



SELECT              
    FileGroup.value('FileName[1]', 'VARCHAR(1000)') FileName, 
 tbl1.Files.value('FilePath[1]', 'VARCHAR(1000)') FilePath,
 tbl2.DependentOnFileNames.value('FileGUID[1]', 'UNIQUEIDENTIFIER') DependentFileGUID
FROM    @xml.nodes('/FileGroups/FileGroup') tbl(FileGroup)
CROSS APPLY tbl.FileGroup.nodes('Files/File') tbl1(Files) 
CROSS APPLY tbl1.Files.nodes('DependentOnFileNames') tbl2(DependentOnFileNames)

Current Result:

Expected Result:

Code Different · Accepted Answer · 2018-08-28T19:19:06.693

There were 2 problems with your last line: (1) it should be an OUTER APPLY because a file may have no dependencies and (2) you need to go one level deeper, into DependentOnFileNames/FileGUID instead of DependentOnFileNames. It should be:

OUTER APPLY tbl1.Files.nodes('DependentOnFileNames/FileGUID') tbl2(DependentOnFileNames)

And modify your SELECT clause accordingly:

tbl2.DependentOnFileNames.value('.', 'UNIQUEIDENTIFIER') DependentFileGUID

For future Googlers, a few tips for dealing with XML in SQL Server:

Use nodes() to expand the nested elements at the XML level
Use value() to get the value of an XML node. The selector inside value(...) always return an array of values, even if it's an array of 1. You must select only 1 element from that array. Element index starts at 1. The exception being value(.), which returns a singleton.
Use CROSS APPLY / OUTER APPLY to expand the nest elements at the SQL level. They act like INNER JOIN / LEFT JOIN, except that there's no need for join conditions.
If you get stuck, put something like T1.[FileGroup].query('.') in the SELECT clause and explore the XML structure.

The code:

SELECT          
                T1.[FileGroup].value('FileName[1]', 'varchar(30)')      AS FileName
        ,       T2.[File].value('FilePath[1]', 'varchar(500)')          AS FilePath
        ,       T2.[File].value('FileGUID[1]', 'UNIQUEIDENTIFIER')      AS FileGUID
        ,       T3.DependentFileGUID.value('.', 'UNIQUEIDENTIFIER')     AS DependentFileGUID
FROM            
                @xml.nodes('FileGroups/FileGroup')                  T1([FileGroup])
CROSS APPLY     T1.[FileGroup].nodes('Files/File')                  T2([File])
OUTER APPLY     T2.[File].nodes('DependentOnFileNames/FileGUID')    T3(DependentFileGUID)

Thank you very much!!! Appreciate tips provided. It will be very useful for other. — Anand, Aug 28 '18 at 20:05
Good answer! +1 form my side. Just one hint: It is in most cases not the best idea to use `'.'` within `.value()`. You might read [this answer](https://stackoverflow.com/a/43242238/5089204) to find some details... — Shnugo, Aug 29 '18 at 07:29

Parse nested xml using sql server

1 Answers1