Generate an array within XML in sql server

Question

I have the following problem:

I am running a Procedure in Sql Sever that generates an XML through the XML PATH command, however, one of the fields is an array, what I got was this:

This select is inside PROC which is what matters to generate XML:

<font face="Courier New" size="2">
<font color = "blue">SELECT</font>&nbsp;<font color = "maroon">order_priority</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">&quot;@ID&quot;</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_number</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_type</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_priority</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_name</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_phone</font><font color = "silver">=</font><font color = "#FF0080"><b>Isnull</b></font><font color = "maroon">(</font><font color = "maroon">order_client_phone</font><font color = "silver">,</font>&nbsp;<font color = "red">''</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_code</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_cpf_cnpj</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_address</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_address_number</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_complement</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_district</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_city</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "fuchsia"><i>Upper</i></font><font color = "maroon">(</font><font color = "maroon">order_client_state</font><font color = "maroon">)</font>&nbsp;<font color = "maroon">order_client_state</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_client_cep</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_exped_name</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_exped_field</font><font color = "silver">=</font><font color = "#FF0080"><b>Isnull</b></font><font color = "maroon">(</font><font color = "maroon">order_exped_field</font><font color = "silver">,</font>&nbsp;<font color = "red">''</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_exped_date</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_erpwms</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_invoices</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">order_items</font>
<br/><font color = "blue">FROM</font>&nbsp;&nbsp;&nbsp;<font color = "maroon">#temp</font>
<br/><font color = "blue">WHERE</font>&nbsp;&nbsp;<font color = "maroon">order_priority</font>&nbsp;<font color = "silver">&lt;</font>&nbsp;<font color = "black">999</font>
<br/><font color = "blue">ORDER</font>&nbsp;&nbsp;<font color = "blue">BY</font>&nbsp;<font color = "maroon">order_priority</font>
<br/><font color = "blue">FOR</font>&nbsp;<font color = "maroon">xml</font>&nbsp;<font color = "maroon">path</font><font color = "maroon">(</font><font color = "red">'Order'</font><font color = "maroon">)</font><font color = "silver">,</font>&nbsp;<font color = "maroon">root</font><font color = "maroon">(</font><font color = "red">'Customers'</font><font color = "maroon">)</font>&nbsp;
</font>

The result is:

But what I need is, where is the field order_invoices XML would look like this:

Could someone help me with some tips and suggestions on how to achieve this result? Thanks in advance!

Answer 1

I got it that way too:
@André Callazzans

Answer 2

Wouldn’t that be something you want?

SELECT order_priority" @ID",
       <br/>order_number, 
       <br/>order_type,
       <br/>order_priority, 
       <br/>order_client_name, 
       <br/>order_client_phone=Isnull(order_client_phone, ''), 
       <br/>order_client_code, 
       <br/>order_client_cpf_cnpj, 
       <br/>order_client_address, 
       <br/>order_client_address_number, 
       <br/>order_client_complement, 
       <br/>order_client_district, 
       <br/>order_client_city, 
       <br/>Upper(order_client_state) order_client_state, 
       <br/>order_client_cep, 
       <br/>order_exped_name, 
       <br/>order_exped_field=Isnull(order_exped_field, ''), 
       <br/>order_exped_date, 
       <br/>order_erpwms, 
       <br/>invoice_number AS "order_invoices/invoice_number", 
       <br/>invoice_serie AS "order_invoices/invoice_serie", 
       <br/>invoice_danfe AS "order_invoices/invoice_danfe", 
       <br/>invoice_value AS "order_invoices/invoice_value", 
       <br/>invoice_ERPWMS AS "order_invoices/invoice_ERPWMS", 
       <br/>order_items 
<br/>FROM   #temp 
<br/>WHERE  order_priority < 999 
<br/>ORDER  BY order_priority 
<br/>FOR xml path('Order'), root('Customers')