XML to SQL 2008r2 issue

Question

由于某种原因，当我运行下面显示的代码时，我无法区分，它没有给我正确的"DE"值，而是重复了"00"的第一个值。 这仅在最后一列中发生。 有什么想法我哪里出错了吗？

USE EDIXML
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT  code,type,name,DE
FROM OPENXML(@hDoc, 'TRN-862/SEG-BSS/DE')
WITH 
(
code [varchar](100) '@code',
type [varchar](100) '@type',
name [varchar](100) '@name',
DE [varchar](100) '../DE'
)
EXEC sp_xml_removedocument @hDoc
GO

下面是 XML 数据：

<TRN-862>
  <SEG-BSS>
    <SEG-INFO code="BSS" name="BEGINNING SEGMENT FOR SHIPPING SCHEDULE" />
    <DE code="0353" name="TRANSACTION SET PURPOSE CODE" type="ID" desc="Original">00</DE>
    <DE code="0127" name="REFERENCE IDENTIFICATION" type="AN">512</DE>
    <DE code="0373" name="DATE" type="DT">20160204</DE>
    <DE code="0675" name="SCHEDULE TYPE QUALIFIER" type="ID" desc="Shipment Based">SH</DE>
    <DE code="0373" name="DATE" type="DT">20160204</DE>
    <DE code="0373" name="DATE" type="DT">20160227</DE>
    <DE code="0328" name="RELEASE NUMBER" type="AN">512</DE>
    <DE code="0127" name="REFERENCE IDENTIFICATION" type="AN" />
    <DE code="0367" name="CONTRACT NUMBER" type="AN" />
    <DE code="0324" name="PURCHASE ORDER NUMBER" type="AN" />
    <DE code="0676" name="SCHEDULE QUANTITY QUALIFIER" type="ID" desc="Actual Discrete Quantities">A</DE>
  </SEG-BSS>

最后，以下是查询提供的内容：

code    type    name    DE
0353    ID  TRANSACTION SET PURPOSE CODE    00
0127    AN  REFERENCE IDENTIFICATION    00
0373    DT  DATE    00
0675    ID  SCHEDULE TYPE QUALIFIER 00
0373    DT  DATE    00
0373    DT  DATE    00
0328    AN  RELEASE NUMBER  00
0127    AN  REFERENCE IDENTIFICATION    00
0367    AN  CONTRACT NUMBER 00
0324    AN  PURCHASE ORDER NUMBER   00
0676    ID  SCHEDULE QUANTITY QUALIFIER 00

Answer 1

这应该有效

EXEC sp_xml_preparedocument
     @hDoc OUTPUT,
     @XML;
SELECT code,
       type,
       name,
       DE
FROM OPENXML(@hDoc, 'TRN-862/SEG-BSS/DE') 
WITH(
code [VARCHAR](100) '@code', 
type [VARCHAR](100) '@type', 
name [VARCHAR](100) '@name', 
DE [VARCHAR](100) '.'
)

Answer 2

OPENXML 已经过时了。您应该使用像这样的"真正的"XML方法

DECLARE @xml XML=
'<TRN-862>
  <SEG-BSS>
    <SEG-INFO code="BSS" name="BEGINNING SEGMENT FOR SHIPPING SCHEDULE" />
    <DE code="0353" name="TRANSACTION SET PURPOSE CODE" type="ID" desc="Original">00</DE>
    <DE code="0127" name="REFERENCE IDENTIFICATION" type="AN">512</DE>
    <DE code="0373" name="DATE" type="DT">20160204</DE>
    <DE code="0675" name="SCHEDULE TYPE QUALIFIER" type="ID" desc="Shipment Based">SH</DE>
    <DE code="0373" name="DATE" type="DT">20160204</DE>
    <DE code="0373" name="DATE" type="DT">20160227</DE>
    <DE code="0328" name="RELEASE NUMBER" type="AN">512</DE>
    <DE code="0127" name="REFERENCE IDENTIFICATION" type="AN" />
    <DE code="0367" name="CONTRACT NUMBER" type="AN" />
    <DE code="0324" name="PURCHASE ORDER NUMBER" type="AN" />
    <DE code="0676" name="SCHEDULE QUANTITY QUALIFIER" type="ID" desc="Actual Discrete Quantities">A</DE>
  </SEG-BSS>
</TRN-862>';
  SELECT DE.value('@code','varchar(max)') AS code
        ,DE.value('@type','varchar(max)') AS type
        ,DE.value('@name','varchar(max)') AS name
        ,DE.value('.','varchar(max)') AS DE
  FROM @xml.nodes('/TRN-862/SEG-BSS/DE') AS One(DE)

结果

code    type    name                            DE
0353    ID      TRANSACTION SET PURPOSE CODE    00
0127    AN      REFERENCE IDENTIFICATION        512
0373    DT      DATE                            20160204
0675    ID      SCHEDULE TYPE QUALIFIER         SH
0373    DT      DATE                            20160204
0373    DT      DATE                            20160227
0328    AN      RELEASE NUMBER                  512
0127    AN      REFERENCE IDENTIFICATION    
0367    AN      CONTRACT NUMBER 
0324    AN      PURCHASE ORDER NUMBER   
0676    ID      SCHEDULE QUANTITY QUALIFIER     A