时间戳

首页 > 解决方案 > XML 到 SQL 问题

sql - XML 到 SQL 问题

问题描述

我正在尝试解析示例 xml，但没有得到预期的输出，我正在使用 openxml 来执行此操作：

这是代码：

    declare @myxml xml =
'<Departments>
   <Department>
    <Employees>
      <Employee user="J" id="J10" method="email" date="06/13/2018 08:59">

      </Employee>
      <Employee user="R" id="R10" method="email1" date="07/13/2018 08:59">

      </Employee>
    </Employees>
  </Department>

  <Department>
    <Employees>
      <Employee user="Jason" id="J101" method="email" date="06/13/2018 08:59">

      </Employee>
      <Employee user="Roy" id="R101" method="email1" date="07/13/2018 08:59">

      </Employee>
    </Employees>
  </Department>
</Departments>'
declare @i int =2;
declare @x_path varchar(5000) = (select  'Departments/Department[' + cast(@i as varchar) + ']' )
DECLARE @hDoc AS INT, @SQL NVARCHAR (MAX)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @myxml
SELECT name,id,method,user_date
FROM OPENXML(@hDoc, @x_path)
WITH 
(
name [varchar](1000) 'Employees/Employee/@user',
id [varchar](1000) 'Employees/Employee/@id',
method [varchar](1000) 'Employees/Employee/@method',
user_date [varchar](1000) 'Employee/Employee/@date'
)
EXEC sp_xml_removedocument @hDoc
go

我只得到 1 行，但我想要 2 行：

上述查询的输出：

name    id  method  user_date
Jason   J101    email   NULL

预期输出：

name    id     method   user_date
Jason   J101    email   06/13/2018 08:59
Roy     R101    email   07/13/2018 08:59

笔记

我只想遍历第二个部门，这就是为什么我在路径中附加了 [@i] 以确保它只遍历第二个部门。并且 i 的值将动态决定，截至目前我已将其设置为 2。

任何帮助，将不胜感激。谢谢

标签： sqlsql-serverxmlsql-server-openxml

解决方案

使用这个 XPath

declare @x_path varchar(5000) = (select 'Departments/Department[' + cast(@i as varchar) + ']/Employees/Employee' )

而这个 Select 语句

SELECT *
FROM OPENXML(@hDoc, @x_path)
WITH 
(
name [varchar](1000) '@user',
id [varchar](1000) '@id',
method [varchar](1000) '@method',
user_date [varchar](1000) '@date'
)

当 id = 2

这将返回

+------+------+--------+------------------+
| name |  id  | method |    user_date     |
+------+------+--------+------------------+
| Json | J101 | email  | 06/13/2018 08:59 |
| Roy  | R101 | email1 | 07/13/2018 08:59 |
+------+------+--------+------------------+

对于 id = 1

+------+-----+--------+------------------+
| name | id  | method |    user_date     |
+------+-----+--------+------------------+
| J    | J10 | email  | 06/13/2018 08:59 |
| R    | R10 | email1 | 07/13/2018 08:59 |
+------+-----+--------+------------------+

推荐阅读