如何在 SQL 中从 XML 中选择值

Question

我试图从 SQL 中的某些 XML 中获取值，但在最后部分中遇到了困难，我怎样才能从中获取值呢？

DECLARE @Info XML
SET @Info=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfDriveData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DriveData>
    <DriveName>C:\</DriveName>
    <DriveSpace>429223288832</DriveSpace>
  </DriveData>
  <DriveData>
    <DriveName>I:\</DriveName>
    <DriveSpace>228759863296</DriveSpace>
  </DriveData>
</ArrayOfDriveData>'


-- Declare a handle for the xml document    
DECLARE @idoc INT

-- Prepare the xml 
EXEC sp_xml_preparedocument @idoc OUTPUT, @Info

SELECT 
    *
FROM 
    OPENXML (@idoc, '/ArrayOfDriveData/DriveData/,3)

我得到的结果是：

id | parentid | nodetype | localname| namespaceuri | datatype | prev | text
4   0   1   DriveData   NULL    NULL    NULL    NULL    NULL
5   4   1   DriveName   NULL    NULL    NULL    NULL    NULL
10  5   3   #text   NULL    NULL    NULL    NULL    C:\
6   4   1   DriveSpace  NULL    NULL    NULL    5   NULL
11  6   3   #text   NULL    NULL    NULL    NULL    429223288832
7   0   1   DriveData   NULL    NULL    NULL    4   NULL
8   7   1   DriveName   NULL    NULL    NULL    NULL    NULL
12  8   3   #text   NULL    NULL    NULL    NULL    I:\
9   7   1   DriveSpace  NULL    NULL    NULL    8   NULL
13  9   3   #text   NULL    NULL    NULL    NULL    228759863296

看来我需要的唯一列是 localname 和 Text，理想情况下我希望结果看起来像：

Drive | Space
    C:\  123456

这可能之前已经被问过，但我看不到任何人回答如何做到这一点（关于如何做的大量数据）从表中获取 XML，关于如何从 XML 获取数据到表中的内容并没有太多介绍），而且我在这方面的经验有点缺乏，所以我一直在思考如何完成这件事。

谢谢

Answer 1

只需简单查询即可。

select
  t.drivedata.value('DriveName[1]', 'varchar(max)'),
  t.drivedata.value('DriveSpace[1]', 'bigint')
from
  @info.nodes('/ArrayOfDriveData/DriveData') as t(drivedata);

Answer 2

如果您想坚持使用 openxml

SELECT 
    DriveName, DriveSpace
FROM 
    OPENXML (@idoc, '/ArrayOfDriveData/DriveData',3)
    with (DriveName varchar(10) 'DriveName', DriveSpace varchar(20) 'DriveSpace)

，当然，可以将 DriveSpace 放入 varchar 中，但您可以将其更改为您需要的任何数据类型。

我还想指出，GSerg 下面发布的内容通常被认为是目前更正确的方法。 OPENXML 是一种较旧的方法。