Generating Nested XML Structure of Orders and Their Details with Custom SQL in Volusion

Question

I am working with Volusion and attempting to extract order data along with their related order details in a nested XML structure using SQL. The aim is to get each order along with its respective order details nested within it.

Here's a simplified version of the SQL query I'm working with:

SELECT
    o.OrderID,
    o.OrderDate,
    o.OrderStatus,
    od.OrderDetailID,
    od.ProductCode
FROM
    Orders o
        LEFT JOIN
    OrderDetails od ON o.OrderID = od.OrderID
WHERE
    o.OrderDate >= '2016-01-01' AND o.OrderDate < '2016-02-01';

However, the XML output I’m receiving from this approach isn’t nested as intended, and instead, every order item produces a new order entry like so:

<Orders>
<OrderID>13971</OrderID>
<OrderDate>1/1/2016 12:09:00 AM</OrderDate>
<OrderStatus>Shipped</OrderStatus>
<OrderDetailID>147436</OrderDetailID>
<ProductCode>First Product</ProductCode>
</Orders>
<Orders>
<OrderID>13971</OrderID>
<OrderDate>1/1/2016 12:09:00 AM</OrderDate>
<OrderStatus>Shipped</OrderStatus>
<OrderDetailID>147437</OrderDetailID>
<ProductCode>Second Product</ProductCode>
</Orders>
...

Ideally, I'm trying to produce an XML structure along these lines:

<Orders>
    <OrderID>13971</OrderID>
    <OrderDate>1/1/2016 12:09:00 AM</OrderDate>
    <OrderStatus>Shipped</OrderStatus>
    <OrderDetails>
        <OrderDetail>
            <OrderDetailID>147436</OrderDetailID>
            <ProductCode>First Product</ProductCode>
        </OrderDetail>
        <OrderDetail>
            <OrderDetailID>147437</OrderDetailID>
            <ProductCode>Second Product</ProductCode>
        </OrderDetail>
        ...
    </OrderDetails>
</Orders>

Despite various approaches and reviewing the Volusion schema, I'm unable to achieve the desired nested XML structure. Volusion support considers this to be an advanced use case and, unfortunately, doesn’t offer support for it.

Has anyone successfully generated a nested XML structure with Volusion data, and could you provide some guidance on how to achieve this?

Thank you in advance for your help!

Answer 1

Try Powershell script :

using assembly System.Xml.Linq
 
$sqlServer = '.\sqlexpress'
$database = 'Order'
$xmlFilename = 'c:\temp\test.xml'

Function CreateXml
{
   param($results, $filename)

   $doc = [System.Xml.Linq.XDocument]::Parse('<Orders></Orders>')

   $orders = $doc.Root
   $groupOrders = $results | Group-Object -Property OrderID

   foreach($group in $groupOrders)
   {
      $newGroup = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('Order'))
      $orders.Add($newGroup)

      $orderId = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('OrderID'), $group.Name.Trim())
      $newGroup.Add($orderId)

      $date = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('OrderDate'), $group.Group[0].OrderDate.ToString('M/d/yyyy hh:mm:ss tt'))
      $newGroup.Add($date[0])

      $status = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('OrderStatus'), $group.Group[0].OrderStatus.Trim())
      $newGroup.Add($status[0])

      $newDetails = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('OrderDetails'))
      $newGroup.Add($newDetails)
      foreach($detail in $group.Group)
      {

         $newDetail = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('OrderDetail'))
         $newDetails.Add($newDetail)

         $detailId = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('OrderDetailID'), $detail.OrderDetailID.Trim())
         $newDetail.Add($detailId)

         $code = [System.Xml.Linq.XElement]::new([System.Xml.Linq.XName]::Get('ProductCode'), $detail.ProductCode.Trim())
         $newDetail.Add($code)

      }      
   }
   $doc.Save($filename)
}

$query = @"

SELECT
    o.OrderID,
    o.OrderDate,
    o.OrderStatus,
    od.OrderDetailID,
    od.ProductCode
FROM
    Orders o
        LEFT JOIN
    OrderDetails od ON o.OrderID = od.OrderID
WHERE
    o.OrderDate >= '2016-01-01' AND o.OrderDate < '2016-02-01';
"@


try
{
    # This is a simple user/pass connection string.
    # Feel free to substitute "Integrated Security=True" for system logins.
    $connString = "Data Source=$sqlServer;Database=$database;Integrated Security = true"

    #Create a SQL connection object
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString

    #Attempt to open the connection
    $conn.Open()
    if($conn.State -eq "Open")
    {
        # We have a successful connection here
        # Notify of successful connection
        Write-Host "Test connection successful"
        $conn.Close()

        $results = Invoke-Sqlcmd -ConnectionString $connString -Query $query

        CreateXml $results $xmlFilename
        
    }
}
catch
{
    $PSItem.Exception.Message
}