时间戳

首页 > 解决方案 > 使用 sql server 解析嵌套的 xml

sql-server - 使用 sql server 解析嵌套的 xml

问题描述

我想使用 SQL 查询解析 XML。下面是查询和 XML。请在下面找到当前结果和预期结果的屏幕截图。它只从嵌套节点中获取一个值。请建议

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)

当前结果：

预期结果：

标签： sql-serverxml

解决方案

您的最后一行存在 2 个问题：（1）它应该是一个，OUTER APPLY因为文件可能没有依赖项，并且（2）您需要更深入一层，DependentOnFileNames/FileGUID而不是DependentOnFileNames. 它应该是：

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

并相应地修改您的SELECT条款：

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

对于未来的 Google 员工，一些在 SQL Server 中处理 XML 的技巧：

  • 用于nodes()扩展 XML 级别的嵌套元素
  • 用于value()获取 XML 节点的值。里面的选择器value(...)总是返回一个值数组，即使它是一个 1 的数组。您必须从该数组中只选择 1 个元素。元素索引从 1 开始。例外是value(.)，它返回一个单例。
  • 用于CROSS APPLY / OUTER APPLY在 SQL 级别展开嵌套元素。它们的行为类似于INNER JOIN / LEFT JOIN，只是不需要加入条件。
  • 如果遇到困难，请T1.[FileGroup].query('.')在SELECT子句中添加类似内容并探索 XML 结构。

编码：

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)

推荐阅读