主页 / dba / 问题 / 321794
Accepted
ChrisF
Asked: 2023-01-07 04:01:07 +0800 CST

无法从存储在表列中的 XML 中读取数据

  • 772

我有一些数据以 XML 格式存储在表中：

<AccountTypes xmlns="">
  <Id>1003</Id>
  <Id>2</Id>
  <Id>3</Id>
  <Id>1004</Id>
  <Id>1002</Id>
  <Id>0</Id>
</AccountTypes>

这是以前仅在 C# 代码中处理的遗留数据，因此无法更改。

我现在需要阅读它并在存储过程中处理它。到目前为止，我有以下代码：

DECLARE @accountTypes XML
SELECT @accountTypes = DT.AccountTypes FROM dbo.DataTable AS DT
WHERE DT.Id = 1016

DECLARE @hdoc int
    
EXEC sys.sp_xml_preparedocument @hdoc OUTPUT, @accountTypes
SELECT *
FROM OPENXML (@hdoc, '/AccountTypes/Id')
WITH(
    Id VARCHAR(10) 'Id'
    )

EXEC sys.sp_xml_removedocument @hdoc

然而，它的输出只是空值行：

Id
NULL
NULL
NULL
NULL
NULL
NULL

而不是我期望的 id 值。

无论是否包含colpattern('Id') 或是否在命令中设置flags参数，我都会得到相同的结果。OPENXML

我可以对当前形式的数据做些什么来从 XML 中获取实际值吗？

sql-server

2 个回答

  1. Best Answer

    Id正在寻找'/AccountTypes/Id'称为节点的子元素Id

    即会匹配一些东西

    <AccountTypes xmlns="">
  <Id>
    <Id>1003</Id>
  </Id>
</AccountTypes>

    您可以使用

    DECLARE @accountTypes XML
SELECT @accountTypes = '<AccountTypes xmlns="">
  <Id>1003</Id>
  <Id>2</Id>
  <Id>3</Id>
  <Id>1004</Id>
  <Id>1002</Id>
  <Id>0</Id>
</AccountTypes>'

DECLARE @hdoc int
    
EXEC sys.sp_xml_preparedocument @hdoc OUTPUT, @accountTypes;

SELECT *
FROM OPENXML (@hdoc, '/AccountTypes/Id')
WITH(
    Id INT '.'
    )

EXEC sys.sp_xml_removedocument @hdoc
    • 5

  2. 还有另一种获取数据的方法：

    DECLARE @accountTypes XML
SELECT @accountTypes = DT.AccountTypes FROM dbo.DataTable AS DT
WHERE DT.Id = 1016

SELECT Id.value('.','int') AS Id
FROM @accountTypes.nodes('/AccountTypes/Id') AS Ids(Id)

    这会产生相同的结果，因为我拥有的数据非常小，几乎在同一时间运行。在较大的 XML 上运行时，查询之间可能存在差异。

    • 0

相关问题