title: How do I extract data from xml using xml value() and openxml method?
body: <p>I have an XML in this format: </p>
  
  <pre><code>        &lt;Root&gt;
             &lt;Node-1&gt;
                &lt;Attribute Name="Header-1"/&gt;
                &lt;Attribute Name="Header-2"/&gt;
                &lt;Attribute Name="Header-3"&gt;Value-1&lt;/Attribute&gt; ------- Head_Name
                &lt;Attribute Name="Header-4"&gt;Value-2&lt;/Attribute&gt; 
                &lt;Attribute Name="Header-5"&gt;value-3&lt;/Attribute&gt;
                &lt;Attribute Name="Id_Header"&gt;10101010&lt;/Attribute&gt;
             &lt;/Node-1&gt;
             &lt;Node-2&gt;
                &lt;Attribute Name="Header-6"&gt;Value-4&lt;/Attribute&gt;
                &lt;Attribute Name="Header-7"/&gt;
                &lt;Attribute Name="Date_Header"&gt;2011-06-04 12:51:54.0&lt;/Attribute&gt;
                &lt;Attribute Name="Header-8"/&gt;
                &lt;Attribute Name="Header-9"&gt;Value-5&lt;/Attribute&gt;
                &lt;Attribute Name="Header-10"&gt;Value-6&lt;/Attribute&gt;
                &lt;Attribute Name="Header-11"&gt;Value-7&lt;/Attribute&gt;
                &lt;Attribute Name="Header-12"/&gt;
                &lt;Attribute Name="Header-13"&gt;Value-8&lt;/Attribute&gt;
                &lt;Attribute Name="Header-14"&gt;Value-9&lt;/Attribute&gt;
                &lt;Attribute Name="Header-15"&gt;Value-10&lt;/Attribute&gt;
             &lt;/Node-2&gt;
             &lt;Sub-Node-2/&gt;
             &lt;Node-3&gt;
                &lt;Sub-Node-3&gt;
                   &lt;Node-1&gt;
                      &lt;Attribute Name="Header-16"&gt;Value-11&lt;/Attribute&gt; ------- type
                      &lt;Attribute Name="Header-17"/&gt;
                      &lt;Attribute Name="Header-18"&gt;Value-12&lt;/Attribute&gt;
                      &lt;Attribute Name="Header-19"&gt;Value-13&lt;/Attribute&gt;
                      &lt;Attribute Name="Header-20"&gt;Value-14&lt;/Attribute&gt; ------- name
                   &lt;/Node-1&gt;
                   &lt;Relationship type="Sub-Header-1"&gt;
                      &lt;Attribute Name="Header-21"&gt;Value-15&lt;/Attribute&gt; ------- Quantity
                   &lt;/Sub-Node-2&gt;
                   &lt;Node-3/&gt;
                &lt;/Sub-Node-3&gt;
                &lt;Sub-Node-3&gt;
                   &lt;Node-1&gt;
                      &lt;Attribute Name="Header-16"&gt;Value-16&lt;/Attribute&gt; ------- type
                      &lt;Attribute Name="Header-17"/&gt;
                      &lt;Attribute Name="Header-18"&gt;Value-17&lt;/Attribute&gt;
                      &lt;Attribute Name="Header-19"&gt;Value-18&lt;/Attribute&gt;
                      &lt;Attribute Name="Header-20"&gt;Value-19&lt;/Attribute&gt; ------- name
                   &lt;/Node-1&gt;
                   &lt;Relationship type="Sub-Header-1"&gt;
                      &lt;Attribute Name="Header-21"&gt;Value-20&lt;/Attribute&gt; ------- Quantity
                   &lt;/Relationship&gt;
                   &lt;Node-3/&gt;
                &lt;/Sub-Node-3&gt;
                &lt;Sub-Node-3&gt;
                   &lt;Node-1&gt;
                      &lt;Attribute Name="Header-16"&gt;Value-21&lt;/Attribute&gt; ------- type
                      &lt;Attribute Name="Header-17"/&gt;
                      &lt;Attribute Name="Header-18"&gt;Value-22&lt;/Attribute&gt;
                      &lt;Attribute Name="Header-19"&gt;Value-23&lt;/Attribute&gt;
                      &lt;Attribute Name="Header-20"&gt;Value-24&lt;/Attribute&gt; ------- name
                   &lt;/Node-1&gt;
                   &lt;Relationship type="Sub-Header-1"&gt;
                      &lt;Attribute Name="Header-21"&gt;Value-25&lt;/Attribute&gt; ------- Quantity
                &lt;Sub-Node-3&gt;
             &lt;/Node-3&gt;
          &lt;/Root&gt;
  </code></pre>
  
  <p>I want the SQL query to display these values:</p>
  
  <p><a href="https://i.stack.imgur.com/LRM4c.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LRM4c.png" alt="enter image description here"></a></p>
  
  <hr>
  
  <p>Using Openxml I tried this:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>Although I get the result, but I get it only for one node. I want result for complete nodes.</p>
  
  <hr>
  
  <p>And using <code>value()</code> method as:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But this gives me this error:</p>
  
  <blockquote>
    <p>Msg 2203, Level 16, State 1, Line 11
    XQuery [value()]: Only '<a href="http://www.w3.org/2001/XMLSchema#decimal" rel="nofollow noreferrer">http://www.w3.org/2001/XMLSchema#decimal</a>?', '<a href="http://www.w3.org/2001/XMLSchema#boolean" rel="nofollow noreferrer">http://www.w3.org/2001/XMLSchema#boolean</a>?' or 'node()*' expressions allowed as predicates, found 'xs:string'</p>
  </blockquote>