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-28 19:19:06Z

2

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)

edited Aug 28, 2018 at 19:19

answered Aug 28, 2018 at 19:07

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anand Over a year ago

Thank you very much!!! Appreciate tips provided. It will be very useful for other.

Gottfried Lesigang Over a year ago

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 to find some details...

Collectives™ on Stack Overflow

Parse nested xml using sql server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related