如何使用xml value（）和openxml方法从xml中提取数据？

我有这种格式的XML：

<Root>
           <Node-1>
              <Attribute Name="Header-1"/>
              <Attribute Name="Header-2"/>
              <Attribute Name="Header-3">Value-1</Attribute> ------- Head_Name
              <Attribute Name="Header-4">Value-2</Attribute> 
              <Attribute Name="Header-5">value-3</Attribute>
              <Attribute Name="Id_Header">10101010</Attribute>
           </Node-1>
           <Node-2>
              <Attribute Name="Header-6">Value-4</Attribute>
              <Attribute Name="Header-7"/>
              <Attribute Name="Date_Header">2011-06-04 12:51:54.0</Attribute>
              <Attribute Name="Header-8"/>
              <Attribute Name="Header-9">Value-5</Attribute>
              <Attribute Name="Header-10">Value-6</Attribute>
              <Attribute Name="Header-11">Value-7</Attribute>
              <Attribute Name="Header-12"/>
              <Attribute Name="Header-13">Value-8</Attribute>
              <Attribute Name="Header-14">Value-9</Attribute>
              <Attribute Name="Header-15">Value-10</Attribute>
           </Node-2>
           <Sub-Node-2/>
           <Node-3>
              <Sub-Node-3>
                 <Node-1>
                    <Attribute Name="Header-16">Value-11</Attribute> ------- type
                    <Attribute Name="Header-17"/>
                    <Attribute Name="Header-18">Value-12</Attribute>
                    <Attribute Name="Header-19">Value-13</Attribute>
                    <Attribute Name="Header-20">Value-14</Attribute> ------- name
                 </Node-1>
                 <Relationship type="Sub-Header-1">
                    <Attribute Name="Header-21">Value-15</Attribute> ------- Quantity
                 </Sub-Node-2>
                 <Node-3/>
              </Sub-Node-3>
              <Sub-Node-3>
                 <Node-1>
                    <Attribute Name="Header-16">Value-16</Attribute> ------- type
                    <Attribute Name="Header-17"/>
                    <Attribute Name="Header-18">Value-17</Attribute>
                    <Attribute Name="Header-19">Value-18</Attribute>
                    <Attribute Name="Header-20">Value-19</Attribute> ------- name
                 </Node-1>
                 <Relationship type="Sub-Header-1">
                    <Attribute Name="Header-21">Value-20</Attribute> ------- Quantity
                 </Relationship>
                 <Node-3/>
              </Sub-Node-3>
              <Sub-Node-3>
                 <Node-1>
                    <Attribute Name="Header-16">Value-21</Attribute> ------- type
                    <Attribute Name="Header-17"/>
                    <Attribute Name="Header-18">Value-22</Attribute>
                    <Attribute Name="Header-19">Value-23</Attribute>
                    <Attribute Name="Header-20">Value-24</Attribute> ------- name
                 </Node-1>
                 <Relationship type="Sub-Header-1">
                    <Attribute Name="Header-21">Value-25</Attribute> ------- Quantity
              <Sub-Node-3>
           </Node-3>
        </Root>

我希望SQL查询显示以下值：

使用Openxml我试过这个：

CREATE TABLE #WorkingTable
    (
        COLUMN1 XML
    )

    INSERT INTO #WorkingTable
    SELECT * FROM OPENROWSET (BULK 'D:\XML_File.xml', SINGLE_BLOB) AS data

    DECLARE @XML AS XML, @docHandle AS INT

    SET @XML = (SELECT COLUMN1 FROM #WorkingTable)

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML

    SELECT *
    FROM OPENXML(@docHandle, 'Root/Node-1',2 )
    WITH 
    (
        Head_Name VARCHAR(255) 'Attribute["Name"] [3]/text()',
        Type VARCHAR(255) '../Node-3/Sub-Node-3/Node-1/Attribute["Name"] [1]/text()',
        Name VARCHAR(255) '../Node-3/Sub-Node-3/Node-1/Attribute["Name"] [5]/text()'
    )

    EXEC sp_xml_removedocument @docHandle

    DROP TABLE #WorkingTable

虽然我得到了结果，但我只得到一个节点 . 我想要完整节点的结果 .

并使用 value() 方法：

CREATE TABLE #WorkingTable
(
    COLUMN1 XML
)
INSERT INTO #WorkingTable(COLUMN1) SELECT * FROM OPENROWSET (BULK 'D:\Xml_File.xml', SINGLE_BLOB) AS DATA
DECLARE @XML AS XML
SET @XML = (SELECT COLUMN1 FROM #WorkingTable)

SELECT col.value('Attribute ["Name"] [5]/text()', 'VARCHAR(255)'),
    col.value('../Node-3/Sub-Node-3/Node-1/Attribute["Name"] [4]/text()','VARCHAR(255)')
FROM @XML.nodes('Root/Node-1') AS ref(col)

DROP TABLE #WorkingTable

但这给了我这个错误：

Msg 2203，Level 16，State 1，Line 11 XQuery [value（）]：只有'http://www.w3.org/2001/XMLSchema#decimal？'，'http：//www.w3.org/ 2001 / XMLSchema的＃布尔？或'node（）*'表达式允许作为谓词，找到'xs：string'