Using OPENXML in SQL Server I am trying to produce a flat representation as shown below.

I've created a simple example here that reproduces my challenge.

The XML example is given here

<HID Name="LEESA5" CodeName="YBAS" Sequences="12,30">
    <Direct Sequence="12">PUNED/BODAS/OSINA/IGDEB/LEESA</Direct>
    <Direct Sequence="30">RUGMU/IGDEB/LEESA</Direct>
</HID>

The SQL Server code I am using is:

declare @idoc int, @XML XML =
    '<HID Name="LEESA5" CodeName="YBAS" Sequences="12,30">
      <Direct Sequence="12">PUNED/BODAS/OSINA/IGDEB/LEESA</Direct>
      <Direct Sequence="30">RUGMU/IGDEB/LEESA</Direct>
    </HID>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @XML  

select * from OPENXML(@idoc,'/HID/*')
with
(
Name varchar(10) '../@Name'
,CodeName varchar(10) '../@CodeName'
,Sequences varchar(10) '../@Sequences'
,Sequence varchar(10) '@Sequence'
,Direct varchar(100) '../Direct'
)

EXEC sp_xml_removedocument @idoc

However the result has the final value repeat rather than use the value from

There is clearly something I don't understand however after hours of searching and fiddling I keep getting the same result. Any pointer towards how to reference the final column values would be most appreciated.

CodePudding user response：

Instead of OPENXML try using the XQuery methods instead, e.g.:

declare @XML XML =
N'<HID Name="LEESA5" CodeName="YBAS" Sequences="12,30">
  <Direct Sequence="12">PUNED/BODAS/OSINA/IGDEB/LEESA</Direct>
  <Direct Sequence="30">RUGMU/IGDEB/LEESA</Direct>
</HID>';

select
  hid.value(N'@Name', N'varchar(10)') as [Name],
  hid.value(N'@CodeName', N'varchar(10)') as [CodeName],
  hid.value(N'@Sequences', N'varchar(10)') as [Sequences],
  direct.value(N'@Sequence', N'varchar(10)') as [Sequence],
  direct.value(N'(text())[1]', N'varchar(100)') as [Direct]
from @XML.nodes(N'/HID') x(hid)
cross apply hid.nodes(N'Direct') y(direct);

Produces: