OPENXML does not return the expected result

Question

I have an 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>

I need to save it as

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

But

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

giving me wrong result:

397300589   673399673   12
397300591   673399669   12

What am I doing wrong? I didn't find same examples, can someone help me with it?

Hi R.Gravis, I just posted one more answer with a performance comparisson... — Shnugo, May 28 '16 at 00:30

Devart · Accepted Answer · 2016-05-30T08:29:25.117

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

UPDATE 30/05/2016

Prepare data:

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 (OLD)

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 (NEW)

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))

Performance comparison (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

I like this too. This approach with `GROUP BY` and the combination of aggregate functions and `CASE` is really handsome... — Shnugo, May 27 '16 at 12:18
@Pinwar13 is a small hack which not enforcing any particular order... in other words... `1/0`, `SELECT 1`, `SELECT NULL` in `ORDER BY` sort rows in natural order ;) — Devart, May 27 '16 at 12:51
i think I overcame the one limitation of your solution, check out my answer. thanks — DaFi4, May 27 '16 at 12:57
@Devart, I'm slightly puzzled... My machine is a quite old Laptop with i5-2410M and 2.3 GHz, (GB DD3 RAM). This is - for sure - slower than yours, but not that much... Did you check your **10.000 rows**? With this amount my machine shows Mik 2.7 / Shn 26 !!! / Mon 37 / DOld 8 and DNew 4.4. This is **~20 times slower** and not linear scaling with your results... One point to mention is, that the solutions do not cast to the same target types. Your solution should read the values as `NVARCHAR` and do the final cast in the top select, shouldn't it (unless all values have the same type)? Greez! — Shnugo, May 30 '16 at 13:47
@Devart, I checked it with alphanumeric `Status` values. Both IDs with `BIGINT` and the status with `NVARCHAR(100)`. 10.000 rows result in Mik 2.93 / Shn 21.3 / Mon 9.8 (**no casts to BIGINT, they cost alot obviously and no pivot**) / DOld 12.3 and DNew 8.2. So in any case Mikaels solutions seems to be the best, if one just wants to read all values top down... Cheerio! — Shnugo, May 30 '16 at 13:54
@Shnugo, yes... several times. When I cast to `NVARCHAR` it's cause server allocate a bit more memory, so spills into tempdb become reality. Anyway it was interesting to meet this challenge :) — Devart, May 30 '16 at 14:08
BTW agree with you, @Mikael Eriksson solution are very good in all situation :))) — Devart, May 30 '16 at 14:10
@Devart, the most interesting part of this challenge is - that I have to get to my boss and ask for a new computer :-) — Shnugo, May 30 '16 at 14:10

DaFi4 · Answer 2 · 2016-05-31T07:58:00.573

I offer for your consideration the following. You may need to pivot the result, but the result has enough meaning to program against as-is.

message_info_position tells you what message_info the value came from and local_position tells you the position within the message_info

I think you will find this solution rather robust and capable of handling odd shapes of your data.

Hope it helps!

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)

Hi montewhizdoh, I just posted one more answer with a performance comparisson... Besides the fact, that this solution is not the best in performance, I really appreciate the tricky code! +1 from my side! I've never seen this: `[. << $i]`. Could you please explain this to me? Maybe you've got a link? — Shnugo, May 28 '16 at 00:32
Hi Shnugo, thanks for the performance comparison. I modified the code to use a method that reduces the dom traversal (increasing performance), while maintaining the robustness of my solution. — DaFi4, May 31 '16 at 07:59
Shnugo, the answer here explains the syntax and a warning about O(n^2) efficiency. I apologize to everyone for not testing my performance first, I was totally only focused on achieving accuracy at the time. I tend to "make things work" first then optimize later http://stackoverflow.com/questions/1134075/finding-node-order-in-xml-document-in-sql-server — DaFi4, May 31 '16 at 08:06

score 3 · Answer 3 · edited Jun 20 '20 at 09:12

This is not an answer, just a performance comparisson!

What I really love on SO is the variety of solutions offered by different people. Here are 4 answers, each of them following a completely different concept.

I was curious how they perform. This is my result:

Mikael Eriksson in ~1.4 secs
Shnugo in ~4.8 secs
montewhizdoh in 8.8 secs but not yet pivoted!!
Devart in 11.3 secs

As there are all nodes taken, approaches reading top-down through the XMLs are obviously faster than approaches with a lot of back and forth navigation. Might be, that some solutions could be improved...

And it was a surprise for me, that the GROUP BY with MAX and CASE was a slow one...

This is the code:

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;

Thanks for you efforts. I suppose that in my example been a some spills into tempdb :) — Devart, May 30 '16 at 08:22
Thanks, Shnugo, I learned a lot from your post about performance testing! — DaFi4, May 31 '16 at 07:59

Shnugo · Answer 4 · 2016-05-27T11:58:47.197

Firs of all: FROM OPENXML is outdated! You should better use the real XML methods...

UPDATE

According to your comment I suggest this: You read all tags in numbered sets and puzzle them together with their index within their sub-set.

I added a third group to the second message_info to test this...

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>
      <status>12</status>
      <id>397300591</id>
      <pdu_id>673399671</pdu_id>
      <status>12</status>
      <id>1111</id>
      <pdu_id>2222</pdu_id>
      <status>33</status>
    </message_info>
  </message_infos>
</response>';

WITH AllMessageInfos AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS Mi_Index
           ,mi.query('.') AS TheMI
    FROM @ixml.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('.','bigint') 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('.','bigint') 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

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>
      <status>12</status>
      <id>397300591</id>
      <pdu_id>673399671</pdu_id>
      <status>12</status>
    </message_info>
  </message_infos>
</response>';

SELECT m.value('id[1]','bigint') AS id
      ,m.value('pdu_id[1]','bigint') AS pdu_id
      ,m.value('status[1]','int') AS status
FROM @ixml.nodes('/response/message_infos/message_info') AS A(m)
UNION ALL
SELECT m.value('id[2]','bigint') AS id
      ,m.value('pdu_id[2]','bigint') AS pdu_id
      ,m.value('status[2]','int') AS status
FROM @ixml.nodes('/response/message_infos/message_info') AS A(m)

I agree, this xml file is not OK. But count of items inside 'message_info' is not fixed. Looks like there's no trivial way to solve the task. — R. Gravis, May 27 '16 at 11:38
@R.Gravis And are there always all three tags as set one after the other? — Shnugo, May 27 '16 at 11:45
Yep, set of tags is fixed, but maximum... actually, this is parts of SMS. How many parts can be in 1 message? I dont know — R. Gravis, May 27 '16 at 11:53
@Shnugo good implementation :) btw my case will not work if at least one tag is not available — Devart, May 27 '16 at 12:09
@Devart Hi, My approach has a big flaw in this case too: As the subsets are bound together by their position, this would pair wrong entries... But OP said *set of tags is fixed*... More important - maybe - is, that your approach does not give back from wich `message_info` the data is taken. — Shnugo, May 27 '16 at 12:17
@Shnugo thank you very much for the help, but Devart's variant is a little bit easier :) — R. Gravis, May 27 '16 at 12:20
@R.Gravis no problem, I voted on it myself... :-) Happy coding — Shnugo, May 27 '16 at 12:24

score 2 · Answer 5 · answered May 27 '16 at 13:26

You can use a number table to fetch the n:th value. This does of course not rely on a undefined order by statements but it does require all elements present equal number of times within each message_info element.

Here I use a static number table of 10 rows.

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 @x.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')

Very interesting solution. Thank you :) – Devart May 30 '16 at 08:23 — Devart, May 30 '16 at 08:23

OPENXML does not return the expected result

5 Answers5

This is not an answer, just a performance comparisson!

UPDATE

previous