title: Postgresql XML-to-table with nested data
body: <p>I have some XML that needs to be inserted into two tables, "products" and "barcodes". The XML looks something like</p>
  
  <pre><code>&lt;products&gt;
    &lt;product&gt;
      &lt;code&gt;0001&lt;/code&gt;
      &lt;name&gt;Prod1&lt;/name&gt;
      &lt;active&gt;t&lt;/active&gt;
      &lt;barcodes&gt;
          &lt;barcode&gt;0001666&lt;/barcode&gt;
          &lt;barcode&gt;6660001&lt;/barcode&gt;
      &lt;/barcodes&gt;
    &lt;product&gt;
    &lt;product&gt;
      ... another one
    &lt;/product&gt;
  </code></pre>
  
  <p></p>
  
  <p>and the product table consist of the three attributes and the barcode table is a simple barcode, product code tuple with a relation the the product table. I can do something like</p>
  
  <pre><code>    insert into product (code, name, active) (
      with products(prod_row) as (select-xml-column-from-table)
          select 
              unnest(xpath('//product/code/text()', prod_row)),
              unnest(xpath('//product/name/text()', prod_row)),
              unnest(xpath('//product/active/text()', prod_row))
          from products
  );
  </code></pre>
  
  <p>and it works for the products but is there a sane way of filling the barcode-table with references to the product code in the same statement?</p>
  
  <p>BTW. I haven't been able to find a cast that would work for an insert into the boolean "active", any pointers?</p>
  
  <p>Thanks in advance,
    Nik</p>