title: XML file import to SQL database with multiple children
body: <p>I'm trying to import an XML file into a SQL Server database. Please have a look on the code and on the xml file. Thanks in advance. </p>
  
  <p>I tried to google lot of pages but all of them deal with only one child (or how to call it). </p>
  
  <p>But In my real xml the structure is like </p>
  
  <pre><code>:1x supplier --&gt; multiple orders --&gt; multiple customers --&gt; multiple products. 
  </code></pre>
  
  <p>We need to create a stored procedure which will be called when we get the XML file. </p>
  
  <p>Thanks again Petr</p>
  
  <p>SQL code:</p>
  
  <pre><code>DROP TABLE #TEST
  
  CREATE TABLE #TEST
  (CUSTID VARCHAR(10),CUSTNAME VARCHAR(50),PRODUCT VARCHAR(500))
  
  INSERT INTO #TEST(CUSTID,CUSTNAME,PRODUCT)
  SELECT
      x.orders.query('order/customer/cust_id').value('.', 'VARCHAR(10)'),
      x.orders.query('order/customer/name').value('.', 'VARCHAR(50)'),
      x.orders.query('order/product_lines/product/supplier_prod_num').
     value('.','VARCHAR(500)') 
  FROM
     (SELECT CAST(x AS XML)
      FROM OPENROWSET(
            BULK '\\test\Test.xml',
            SINGLE_BLOB) AS T(x)) AS T(x) 
      CROSS APPLY
          x.nodes('order_batch/orders/order/product_lines/product') AS x(orders)
  
  select * from #test
  </code></pre>
  
  <p>XML file to test:</p>
  
  <pre><code>&lt;order_batch&gt;
    &lt;order_header&gt;
      &lt;doc_format_ver&gt;1.0&lt;/doc_format_ver&gt;
      &lt;originating_software&gt;
        &lt;software_name&gt;test&lt;/software_name&gt;
        &lt;software_ver&gt;2.0&lt;/software_ver&gt;
      &lt;/originating_software&gt;
    &lt;/order_header&gt;
    &lt;supplier&gt;
      &lt;cp_supplier_id&gt;93&lt;/cp_supplier_id&gt;
      &lt;name&gt;supname&lt;/name&gt;
      &lt;address_line_1&gt;address_line_1&lt;/address_line_1&gt;
      &lt;address_line_2&gt;address_line_1&lt;/address_line_2&gt;
    &lt;/supplier&gt;
    &lt;orders&gt;
      &lt;order o_count="1"&gt;
        &lt;customer&gt;
          &lt;cust_id&gt;7240&lt;/cust_id&gt;
          &lt;name&gt;CustNamek&lt;/name&gt;
          &lt;address_line_1&gt;address_line_1&lt;/address_line_1&gt;
          &lt;address_line_2&gt;address_line_1&lt;/address_line_2&gt;
        &lt;/customer&gt;
        &lt;order_details&gt;
          &lt;date_created&gt;2012-10-08&lt;/date_created&gt;
          &lt;time_created&gt;05:37:11&lt;/time_created&gt;
        &lt;/order_details&gt;
        &lt;product_lines&gt;
          &lt;product p_count="1"&gt;
            &lt;supplier_prod_num&gt;15115&lt;/supplier_prod_num&gt;
            &lt;qty_type&gt;E&lt;/qty_type&gt;
          &lt;/product&gt;
          &lt;product p_count="2"&gt;
            &lt;supplier_prod_num&gt;010211&lt;/supplier_prod_num&gt;
            &lt;qty_type&gt;E&lt;/qty_type&gt;
          &lt;/product&gt;
        &lt;/product_lines&gt;
      &lt;/order&gt;
      &lt;order o_count="2"&gt;
        &lt;customer&gt;
          &lt;cust_id&gt;7238&lt;/cust_id&gt;
          &lt;name&gt;custname&lt;/name&gt;
          &lt;address_line_1&gt;address_line_1&lt;/address_line_1&gt;
          &lt;address_line_2&gt;address_line_1&lt;/address_line_2&gt;
        &lt;/customer&gt;
        &lt;order_details&gt;
          &lt;supplier_ref&gt;0093513982&lt;/supplier_ref&gt;
          &lt;delivery_date&gt;2012-10-08&lt;/delivery_date&gt;
          &lt;special_instructions /&gt;
        &lt;/order_details&gt;
        &lt;product_lines&gt;
          &lt;product p_count="1"&gt;
            &lt;supplier_prod_num&gt;6748&lt;/supplier_prod_num&gt;
            &lt;qty_type&gt;E&lt;/qty_type&gt;
          &lt;/product&gt;
          &lt;product p_count="2"&gt;
            &lt;supplier_prod_num&gt;6744&lt;/supplier_prod_num&gt;
            &lt;qty_type&gt;E&lt;/qty_type&gt;
          &lt;/product&gt;
        &lt;/product_lines&gt;
      &lt;/order&gt;
    &lt;/orders&gt;
  &lt;/order_batch&gt;
  </code></pre>