文章/答案/技术大牛

发布

社区首页 >问答首页 >OPENXML不返回预期的结果。

问OPENXML不返回预期的结果。
EN

Stack Overflow用户

提问于 2016-05-27 11:24:58

回答 5查看 608关注 0票数 4

我有一个XML

<response>
  <message_infos>
    <message_info>
      <id>397300589</id>
      <pdu_id>673399673</pdu_id>
      <status>12</status>
      <id>397300589</id>
      <pdu_id>673399675</pdu_id>
      <status>12</status>
    </message_info>
    <message_info>
      <id>397300591</id>
      <pdu_id>673399669</pdu_id>
      <status>12</status>
      <id>397300591</id>
      <pdu_id>673399671</pdu_id>
      <status>12</status>
    </message_info>
  </message_infos>
</response>

我要把它存起来

397300589   673399673   12
397300589   673399675   12
397300591   673399669   12
397300591   673399671   12

但

SELECT *
FROM OPENXML(@ixml, '/response/message_infos/message_info')
WITH (id VARCHAR(50) 'id', pdu_id VARCHAR(50) 'pdu_id', status INT 'status')

给了我错误的结果：

397300589   673399673   12
397300591   673399669   12

我做错了什么？我没有找到同样的例子，有人能帮我吗？

sql-server

xml

tsql

xquery

openxml

回答 5

Stack Overflow用户

回答已采纳

发布于 2016-05-27 12:06:47

DECLARE @xml XML = N'
<response>
  <message_infos>
    <message_info>
      <id>397300589</id>
      <pdu_id>673399673</pdu_id>
      <status>12</status>
      <id>397300589</id>
      <pdu_id>673399675</pdu_id>
      <status>12</status>
    </message_info>
    <message_info>
      <id>397300591</id>
      <pdu_id>673399669</pdu_id>
      <status>12</status>
      <id>397300591</id>
      <pdu_id>673399671</pdu_id>
      <status>12</status>
    </message_info>
  </message_infos>
</response>'

SELECT
      id = MIN(CASE WHEN RowNumGroup = 1 THEN val END)
    , pdu_id = MIN(CASE WHEN RowNumGroup = 2 THEN val END)
    , [status] = MIN(CASE WHEN RowNumGroup = 0 THEN val END)
FROM (
    SELECT
          val = t.c.value('(./text())[1]', 'BIGINT')
        , RowNum = ROW_NUMBER() OVER (ORDER BY 1/0)
        , RowNumGroup = ROW_NUMBER() OVER (ORDER BY 1/0) % 3
    FROM @xml.nodes('/response/message_infos/message_info/*') t(c)
) t
GROUP BY RowNum - CASE WHEN RowNumGroup = 0 THEN 3 ELSE RowNumGroup END

更新30/05/2016

准备数据：

SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#xml') IS NOT NULL
    DROP TABLE #xml
GO
CREATE TABLE #xml (data XML)
GO
INSERT INTO #xml
SELECT TOP(10000) N'
<response>
  <message_infos>
    <message_info>
      <id>397300589</id>
      <pdu_id>673399673</pdu_id>
      <status>12</status>
      <id>397300589</id>
      <pdu_id>673399675</pdu_id>
      <status>12</status>
    </message_info>
    <message_info>
      <id>397300591</id>
      <pdu_id>673399669</pdu_id>
      <status>12</status>
      <id>397300591</id>
      <pdu_id>673399671</pdu_id>
      <status>12</status>
    </message_info>
  </message_infos>
</response>'
FROM [master].dbo.spt_values

Mikael Eriksson

DECLARE @d DATETIME = GETDATE()

SELECT T.X.value('(id/text())[sql:column("N.N")][1]', 'INT') AS id,
       T.X.value('(pdu_id/text())[sql:column("N.N")][1]', 'INT') AS pdu_id,
       T.X.value('(status/text())[sql:column("N.N")][1]', 'INT') AS [status]
FROM #xml x
CROSS APPLY data.nodes('/response/message_infos/message_info') AS T(X)
CROSS APPLY (
    VALUES
        (1),(2),(3),(4),(5),
        (6),(7),(8),(9),(10)
) AS N(N)
WHERE N.N <= T.X.value('count(id)', 'INT')

SELECT 'Mikael Eriksson: ' +  CAST(CAST(GETDATE() - @d AS TIME) AS VARCHAR(100))
GO

Shnugo

DECLARE @d DATETIME = GETDATE()

WITH AllMessageInfos AS (
    SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS Mi_Index
         , mi.query('.') AS TheMI
    FROM #xml x
    CROSS APPLY data.nodes('/response/message_infos/message_info') AS A(mi)
)
, AllIDs AS (
    SELECT Mi_Index
         , ROW_NUMBER() OVER(PARTITION BY Mi_Index ORDER BY (SELECT NULL)) AS id_Index
         , id.value('.','NVARCHAR(MAX)') AS id
    FROM AllMessageInfos
    CROSS APPLY TheMI.nodes('message_info/id') AS a(id)   
)
, AllPdu_IDs AS (
    SELECT Mi_Index
         , ROW_NUMBER() OVER(PARTITION BY Mi_Index ORDER BY (SELECT NULL)) AS pdu_Index
         , id.value('.','NVARCHAR(MAX)') AS pdu_id
    FROM AllMessageInfos
    CROSS APPLY TheMI.nodes('message_info/pdu_id') AS a(id)   
)
, AllStatus AS (
    SELECT Mi_Index
         , ROW_NUMBER() OVER(PARTITION BY Mi_Index ORDER BY (SELECT NULL)) AS status_Index
         , id.value('.', 'INT') AS status
    FROM AllMessageInfos
    CROSS APPLY TheMI.nodes('message_info/status') AS a(id)   
)
SELECT i.Mi_Index AS MessageInfoIndex
     , i.id_Index AS SubSetIndex
     , i.id
     , p.pdu_id
     , s.[status]
FROM AllIDs AS i
JOIN AllPdu_IDs AS p ON i.Mi_Index = p.Mi_Index AND i.id_Index = p.pdu_Index
JOIN AllStatus AS s ON i.Mi_Index = s.Mi_Index AND i.id_Index = s.status_Index

SELECT 'Shnugo: ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100))
GO

Montewizdoh

DECLARE @d DATETIME = GETDATE()

SELECT 
    m.value('for $i in . return count(../../*[. << $i])', 'INT') AS message_info_position,
    m.value('for $i in . return count(../*[. << $i]) + 1', 'INT') AS internal_position,
    m.value('fn:local-name(.)', 'SYSNAME') AS element_name,
    m.value('.', 'NVARCHAR(4000)') AS element_value
FROM #xml x
CROSS APPLY data.nodes('/response/message_infos/message_info/*') AS A(m)

SELECT 'Montewhizdoh: ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100))
GO

Devart (旧)

DECLARE @d DATETIME = GETDATE()
SELECT
      id = MAX(CASE WHEN name = 'id' THEN val END)
    , pdu_id = MAX(CASE WHEN name = 'pdu_id' THEN val END)
    , [status] = MAX(CASE WHEN name = 'status' THEN val END)
FROM (
    SELECT
          name = t.c.value('local-name(.)', 'SYSNAME')
        , val = t.c.value('.', 'INT')
        , RowNum = ROW_NUMBER() OVER (ORDER BY 1/0)
        , RowNumGroup = ROW_NUMBER() OVER (ORDER BY 1/0) % 3
    FROM #xml x
    CROSS APPLY data.nodes('/response/message_infos/message_info/*') t(c)
) t
GROUP BY RowNum - CASE WHEN RowNumGroup = 0 THEN 3 ELSE RowNumGroup END
SELECT 'Devart (OLD): ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100))
GO

Devart (新)

DECLARE @d DATETIME = GETDATE()
SELECT
      id = MIN(CASE WHEN RowNumGroup = 1 THEN val END)
    , pdu_id = MIN(CASE WHEN RowNumGroup = 2 THEN val END)
    , [status] = MIN(CASE WHEN RowNumGroup = 0 THEN val END)
FROM (
    SELECT
          val = t.c.value('(./text())[1]', 'BIGINT')
        , RowNum = ROW_NUMBER() OVER (ORDER BY 1/0)
        , RowNumGroup = ROW_NUMBER() OVER (ORDER BY 1/0) % 3
    FROM #xml x
    CROSS APPLY data.nodes('/response/message_infos/message_info/*') t(c)
) t
GROUP BY RowNum - CASE WHEN RowNumGroup = 0 THEN 3 ELSE RowNumGroup END

SELECT 'Devart (NEW): ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100))

性能比较 (Core i5 4460 3.2GHz、DDR3 8Gb、SQL Server 2014 SP1 Express)：

Mikael Eriksson: 00:00:00.327
Shnugo:          00:00:00.913
Montewhizdoh:    00:00:01.680
Devart (OLD):    00:00:00.363
Devart (NEW):    00:00:00.200

票数 4

Stack Overflow用户

发布于 2016-05-27 12:27:43

我建议你考虑以下几点。您可能需要将结果转换为支点，但结果有足够的意义来针对原样进行编程。

message_info_position告诉您值来自什么，local_position告诉您message_info中的位置

我认为您会发现这个解决方案相当健壮，并且能够处理数据的奇怪形状。

希望能帮上忙！

DECLARE @ixml XML=
'<response>
  <message_infos>
    <message_info>
      <id>397300589</id>
      <pdu_id>673399673</pdu_id>
      <status>12</status>
      <id>397300589</id>
      <pdu_id>673399675</pdu_id>
      <status>12</status>
    </message_info>
    <message_info>
      <id>397300591</id>
      <pdu_id>673399669</pdu_id>
      <id>397300591</id>
      <pdu_id>673399671</pdu_id>
      <status>12</status>
    </message_info>
    <message_info>
      <id>3973005891</id>
      <pdu_id>6733996732</pdu_id>
      <status>123</status>
      <id>3973005894</id>
      <pdu_id>6733996755</pdu_id>
      <status>126</status>
      <id>3973005897</id>
      <pdu_id>6733996738</pdu_id>
      <status>129</status>
      <id>39730058912</id>
      <pdu_id>67339967513</pdu_id>
    <pdu_id>67339967513x</pdu_id>
    <pdu_id>67339967513y</pdu_id>
      <status>12</status>
    </message_info>
  </message_infos>
</response>';

SELECT 
 row_number() over(order by A.m) as internal_position,
 CHECKSUM(m.value('..', 'nvarchar(max)')) as message_info_position,
 m.value('fn:local-name(.)', 'SYSNAME') as element_name,
 m.value('.', 'nvarchar(4000)') as element_value
FROM @ixml.nodes('/response/message_infos/message_info/*') AS A(m)

票数 3

Stack Overflow用户

发布于 2016-05-28 00:29:41

这不是一个答案，只是一个性能比较！

我真正喜欢的是不同的人提供的各种各样的解决方案。这里有四个答案，每一个都遵循一个完全不同的概念。

我很好奇他们的表现。这是我的结果：

Mikael Eriksson在~1.4秒内
Shnugo in ~4.8秒
在8.8秒中的montewhizdoh，但还没有旋转！！
11.3秒中的Devart

由于所有节点都已被占用，通过XML自上而下读取的方法显然比具有大量来回导航的方法更快。可能是，一些解决方案可以改进.

对我来说这是一个惊喜，GROUP BY with MAX and CASE是一个缓慢的.

这是代码：

CREATE TABLE #tmp (ID INT IDENTITY,XmlContent XML);
GO
INSERT INTO #tmp(XmlContent)
SELECT('<response>
  <message_infos>
    <message_info>
      <id>397300589</id>
      <pdu_id>673399673</pdu_id>
      <status>12</status>
      <id>397300589</id>
      <pdu_id>673399675</pdu_id>
      <status>12</status>
    </message_info>
    <message_info>
      <id>397300591</id>
      <pdu_id>673399669</pdu_id>
      <status>12</status>
      <id>397300591</id>
      <pdu_id>673399671</pdu_id>
      <status>12</status>
    </message_info>
    <message_info>
      <id>3973005891</id>
      <pdu_id>6733996732</pdu_id>
      <status>123</status>
      <id>3973005894</id>
      <pdu_id>6733996755</pdu_id>
      <status>126</status>
      <id>3973005897</id>
      <pdu_id>6733996738</pdu_id>
      <status>129</status>
      <id>39730058912</id>
      <pdu_id>67339967513</pdu_id>
      <status>12</status>
    </message_info>
  </message_infos>
</response>');
GO 1000


--Mikael Eriksson 1,4
DECLARE @d DATETIME = GETDATE();
WITH Numbers AS (SELECT N FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) as tbl(N)) 
select T.X.value('(id/text())[sql:column("N.N")][1]', 'nvarchar(max)') as id,
       T.X.value('(pdu_id/text())[sql:column("N.N")][1]', 'nvarchar(max)') as pdu_id,
       T.X.value('(status/text())[sql:column("N.N")][1]', 'int') as status
from #tmp
CROSS APPLY XmlContent.nodes('/response/message_infos/message_info') as T(X)
  cross apply (SELECT TOP(T.X.value('count(id)', 'int')) N FROM Numbers) AS N(N)

SELECT 'Mikael Eriksson: ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100));
GO

--Shnugo 4.8 Sekunden
DECLARE @d DATETIME = GETDATE();
WITH AllMessageInfos AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS Mi_Index
           ,mi.query('.') AS TheMI
    FROM #tmp
    CROSS APPLY XmlContent.nodes('/response/message_infos/message_info') AS A(mi)
)
,AllIDs AS
(
    SELECT Mi_Index
          ,ROW_NUMBER() OVER(PARTITION BY Mi_Index ORDER BY (SELECT NULL)) AS id_Index
          ,id.value('.','nvarchar(max)') AS id
    FROM AllMessageInfos
    CROSS APPLY TheMI.nodes('message_info/id') AS a(id)   
)
,AllPdu_IDs AS
(
    SELECT Mi_Index
          ,ROW_NUMBER() OVER(PARTITION BY Mi_Index ORDER BY (SELECT NULL)) AS pdu_Index
          ,id.value('.','nvarchar(max)') AS pdu_id
    FROM AllMessageInfos
    CROSS APPLY TheMI.nodes('message_info/pdu_id') AS a(id)   
)
,AllStatus AS
(
    SELECT Mi_Index
          ,ROW_NUMBER() OVER(PARTITION BY Mi_Index ORDER BY (SELECT NULL)) AS status_Index
          ,id.value('.','int') AS status
    FROM AllMessageInfos
    CROSS APPLY TheMI.nodes('message_info/status') AS a(id)   
)
SELECT i.Mi_Index AS MessageInfoIndex
      ,i.id_Index AS SubSetIndex
      ,i.id
      ,p.pdu_id
      ,s.status
FROM AllIDs AS i
INNER JOIN AllPdu_IDs AS p ON i.Mi_Index=p.Mi_Index AND i.id_Index=p.pdu_Index
INNER JOIN AllStatus AS s ON i.Mi_Index=s.Mi_Index AND i.id_Index=s.status_Index

SELECT 'Shnugo: ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100));
GO

--Montewizdoh 8.8 Sekunden
DECLARE @d DATETIME = GETDATE();
SELECT 
m.value('for $i in . return count(../../*[. << $i])', 'int') as message_info_position,
m.value('for $i in . return count(../*[. << $i]) + 1', 'int') as internal_position,
m.value('fn:local-name(.)', 'SYSNAME') as element_name,
m.value('.', 'nvarchar(4000)') as element_value
FROM #tmp
CROSS APPLY XmlContent.nodes('/response/message_infos/message_info/*') AS A(m)
SELECT 'Montewhizdoh: ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100));
GO

--Devart 11.3 Sec
DECLARE @d DATETIME = GETDATE();
SELECT
      id = MAX(CASE WHEN name = 'id' THEN val END)
    , pdu_id = MAX(CASE WHEN name = 'pdu_id' THEN val END)
    , [status] = MAX(CASE WHEN name = 'status' THEN val END)
FROM (
    SELECT
          name = t.c.value('local-name(.)', 'SYSNAME')
        , val = t.c.value('.', 'nvarchar(max)')
        , RowNum = ROW_NUMBER() OVER (ORDER BY 1/0)
        , RowNumGroup = ROW_NUMBER() OVER (ORDER BY 1/0) % 3
    FROM #tmp
    CROSS APPLY XmlContent.nodes('/response/message_infos/message_info/*') t(c)
) t
GROUP BY RowNum - CASE WHEN RowNumGroup = 0 THEN 3 ELSE RowNumGroup END
SELECT 'Devart: ' +  CAST(CAST(GETDATE()-@d AS TIME) AS VARCHAR(100));

GO

DROP TABLE #tmp;

票数 3

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/37482259

复制

相似问题

问OPENXML不返回预期的结果。
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问OPENXML不返回预期的结果。EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问OPENXML不返回预期的结果。
EN