title: Openxmls in SQL Server - drilling down in WITH clause
body: <p>I have an XML like</p>
  
  <pre><code>&lt;root sxfversion="1.6" gendate="2020-01-23 16:16:40"&gt;
  &lt;products&gt;
      &lt;p&gt;
        &lt;id&gt;8244&lt;/id&gt;
        &lt;name&gt;
          NAME OF ARTICLE
        &lt;/name&gt;
        &lt;reference&gt;622801&lt;/reference&gt;
        &lt;ean13&gt;3232870162282&lt;/ean13&gt;
        &lt;stock&gt;17&lt;/stock&gt;
        &lt;price&gt;
          &lt;tax&gt;23&lt;/tax&gt;
          &lt;netto&gt;9.2&lt;/netto&gt;
          &lt;brutto&gt;11.32&lt;/brutto&gt;
        &lt;/price&gt;
       &lt;/p&gt;
     &lt;/products&gt;
  &lt;/root&gt; 
  </code></pre>
  
  <p>I try make a query, for example:</p>
  
  <pre><code>SELECT 
      *
  FROM
      OPENXML(@int, '/root/products/p', 2)
          WITH 
              (id int,
               name varchar(40),
               ean13 varchar(20)
              )
  </code></pre>
  
  <p>and it works. </p>
  
  <p><a href="https://i.stack.imgur.com/9iOpB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9iOpB.png" alt="enter image description here"></a></p>
  
  <p>Now I want use another (nested) column - price (tax, netto, brutto).</p>
  
  <p>I can do it with modifying of query like this (drilling <strong>up</strong>) and it also works:</p>
  
  <pre><code>SELECT 
      *
  FROM
  OPENXML(@int, '/root/products/p/price', 2)
  WITH
  (   
      id int '../id',
      name varchar(40) '../name',
      ean13 varchar(20) '../ean13',
      netto varchar(10),
      brutto varchar(10),
      tax varchar(10)
  )
  </code></pre>
  
  <p>BUT why something like this (drilling <strong>down</strong> doesn't work properly? It duplicate value of price each row to all rows.</p>
  
  <p>Is possible to drill <strong>down</strong>?</p>
  
  <pre><code>SELECT *
  FROM OPENXML(@int, '/root/products/p', 2)
  WITH ( id varchar(10), 
  name varchar(40), 
  ean13 varchar(20),
  netto varchar(10) '../p/price/netto', 
  brutto varchar(10) '//price/brutto', 
  tax varchar(10) '//price/tax')
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/TYOTE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TYOTE.png" alt="enter image description here"></a></p>