SQL query on an XML field with array

Hi all,

I was hoping you could help with an SQL query I need to write.

In the XML I have an array as follows:

<data formcode="CSP1010_01" formname="Uniform Order">
  <item name="submitdate">2015-10-28T07:05:40.246Z</item>
  <item name="form-data-guid">F13051AF-BB4E-403C-8439-CF3250921FD9</item>
  <item name="employeename">Alan Wells</item>
  <item name="embroideredname">Wellsy</item>
  <item name="employeephonenumber">0414920461</item>
  <item name="siteprojectlocation">A002 - Brisbane Admin / Corporate</item>
  <item name="companyentity">Bros</item>
  <item name="order_date">2015-10-28</item>
  <array name="OrderDetails">
    <set>
      <item name="uniformitems">Site PPE: Shirt: Hi-Vis: Mens: Orange/Navy</item>
    </set>
    <set>
      <item name="uniformitems">Chef / Food Prep: Apron: Cotton Drill Full Bib: Mens/Womens: Black</item>
    </set>
    <set>
      <item name="uniformitems">Chef / Food Prep: Shirt: Cotton Drill Closed Front Long Sleeve: Mens/Womens: Khaki</item>
    </set>
  </array>
  <item name="field_d9b92828-6d16-4ee8-9902-8c799c163c19" />
  <item name="manageremail">tuser@ostwaldbros.com.au</item>
  <item name="field_c71c41a1-6ff5-42b5-b4e5-645172924255" />
</data>

Select allOpen in new window

I have the following SQL query to extract but this is a bit dodgy and if more than 6 items are in the order these will not be in the query.

SELECT        
FormXML.query('/data/item[@name="form-data-guid"]/text()') AS FormGuid, 
FormXML.query('/data/array[@name="OrderDetails"]/set[1]/item[@name="uniformitems"]/text()') AS Item1,                      
FormXML.query('/data/array[@name="OrderDetails"]/set[2]/item[@name="uniformitems"]/text()') AS Item2, 
FormXML.query('/data/array[@name="OrderDetails"]/set[3]/item[@name="uniformitems"]/text()') AS Item3, 
FormXML.query('/data/array[@name="OrderDetails"]/set[4]/item[@name="uniformitems"]/text()') AS Item4, 
FormXML.query('/data/array[@name="OrderDetails"]/set[5]/item[@name="uniformitems"]/text()') AS Item5, 
FormXML.query('/data/array[@name="OrderDetails"]/set[6]/item[@name="uniformitems"]/text()') AS Item6
FROM 
dbo.FORMINBOUND
WHERE 
FormCode = 'CSP1010_01'

Select allOpen in new window

Ideally the query would produce an SQL result as follows no matter how many items are ordered.

form-data-guid				                        OrderItem
F13051AF-BB4E-403C-8439-CF3250921FD9 	Site PPE: Shirt: Hi-Vis: Mens: Orange/Navy</item>
F13051AF-BB4E-403C-8439-CF3250921FD9	Chef / Food Prep: Apron: Cotton Drill Full Bib: Mens/Womens: Black
F13051AF-BB4E-403C-8439-CF3250921FD9	Chef / Food Prep: Shirt: Cotton Drill Closed Front Long Sleeve: Mens/Womens: Khaki

Select allOpen in new window

Any help appreciated.

Cheers,
Alan