如何将含多层节点的XML API响应插入SQL数据表？

原XML数据 #

<events>
  <item0>
    <PermitId>705130</PermitId>
    <UserID>389230</UserID>
    <CarID>348729</CarID>
    <StartDate>2022-11-22</StartDate>
    <EndDate>2022-11-22</EndDate>
    <RequestDate>2022-02-22</RequestDate>
    <PermitName>Leisure Visit</PermitName>
    <ExpectedTime>13</ExpectedTime>
    <Companions>6</Companions>
    <StatusID>1</StatusID>
    <Status>Active</Status>
    <City>Makkah</City>
  </item0>
  <item1>
    <PermitId>846926</PermitId>
    <UserID>281556</UserID>
    <CarID>403407</CarID>
    <StartDate>2022-10-23</StartDate>
    <EndDate>2022-12-31</EndDate>
    <RequestDate>2022-10-23</RequestDate>
    <PermitName>Yearly Permit</PermitName>
    <ExpectedTime>0</ExpectedTime>
    <Companions>0</Companions>
    <StatusID>1</StatusID>
    <Status>Active</Status>
    <City>Jeddah</City>
  </item1>
</events>

原SQL语句的问题 #

原语句中OPENXML(@xml,'//*')的XPath表达式//*会匹配XML中所有节点，包括<events>根节点、<item0>、<item1>以及它们的所有子节点，无法正确将每个item节点映射为一条数据记录，导致解析失败。

方法一：修正OPENXML写法 #

指定正确的XPath路径匹配所有以item开头的子节点，同时明确每个字段的映射路径：

DECLARE @xml XML = '你的XML内容';
DECLARE @hdoc INT;

-- 初始化XML文档
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

SELECT *
FROM OPENXML(@hdoc, '//events/*[starts-with(local-name(), "item")]')
WITH (  
  [PermitId] INT 'PermitId',
  [UserID] INT 'UserID',
  [CarID] NVARCHAR(50) 'CarID',
  [StartDate] DATE 'StartDate',
  [EndDate] DATE 'EndDate',
  [RequestDate] DATE 'RequestDate',
  [PermitName] NVARCHAR(100) 'PermitName',
  [ExpectedTime] INT 'ExpectedTime',
  [Companions] INT 'Companions',
  [StatusID] INT 'StatusID',
  [Status] NVARCHAR(100) 'Status',
  [City] NVARCHAR(100) 'City'
);

-- 释放XML文档资源
EXEC sp_xml_removedocument @hdoc;

方法二：使用更现代的XQuery方法（推荐） #

SQL Server支持直接对XML类型变量使用XQuery查询，无需依赖sp_xml_preparedocument，写法简洁且性能更优：

DECLARE @xml XML = '你的XML内容';

SELECT
  item.value('(PermitId/text())[1]', 'INT') AS PermitId,
  item.value('(UserID/text())[1]', 'INT') AS UserID,
  item.value('(CarID/text())[1]', 'NVARCHAR(50)') AS CarID,
  item.value('(StartDate/text())[1]', 'DATE') AS StartDate,
  item.value('(EndDate/text())[1]', 'DATE') AS EndDate,
  item.value('(RequestDate/text())[1]', 'DATE') AS RequestDate,
  item.value('(PermitName/text())[1]', 'NVARCHAR(100)') AS PermitName,
  item.value('(ExpectedTime/text())[1]', 'INT') AS ExpectedTime,
  item.value('(Companions/text())[1]', 'INT') AS Companions,
  item.value('(StatusID/text())[1]', 'INT') AS StatusID,
  item.value('(Status/text())[1]', 'NVARCHAR(100)') AS Status,
  item.value('(City/text())[1]', 'NVARCHAR(100)') AS City
FROM @xml.nodes('/events/*[starts-with(local-name(), "item")]') AS T(item);

插入数据表的写法 #

如果要将解析后的数据插入目标表（例如名为Permits的表），只需将上述SELECT语句改为INSERT INTO：

-- 以XQuery方法为例
DECLARE @xml XML = '你的XML内容';

INSERT INTO Permits (PermitId, UserID, CarID, StartDate, EndDate, RequestDate, PermitName, ExpectedTime, Companions, StatusID, Status, City)
SELECT
  item.value('(PermitId/text())[1]', 'INT'),
  item.value('(UserID/text())[1]', 'INT'),
  item.value('(CarID/text())[1]', 'NVARCHAR(50)'),
  item.value('(StartDate/text())[1]', 'DATE'),
  item.value('(EndDate/text())[1]', 'DATE'),
  item.value('(RequestDate/text())[1]', 'DATE'),
  item.value('(PermitName/text())[1]', 'NVARCHAR(100)'),
  item.value('(ExpectedTime/text())[1]', 'INT'),
  item.value('(Companions/text())[1]', 'INT'),
  item.value('(StatusID/text())[1]', 'INT'),
  item.value('(Status/text())[1]', 'NVARCHAR(100)'),
  item.value('(City/text())[1]', 'NVARCHAR(100)')
FROM @xml.nodes('/events/*[starts-with(local-name(), "item")]') AS T(item);

内容的提问来源于stack exchange，提问作者AH.