Tuesday

How to add subsections in 'for xml' sql statement

SELECT 
    O.OrderID AS '@OrderID',
    O.OrderDate AS '@OrderDate',
    (
        SELECT
            OD.ProductID AS '@ProductID',
            OD.Quantity AS '@Quantity'
        FROM OrderDetails OD
        WHERE OD.OrderID = O.OrderID
        FOR XML PATH('OrderDetail'), TYPE
    ) AS 'OrderDetails'
FROM Orders O
FOR XML PATH('Order'), ROOT('Orders')

result



<Orders>
  <Order OrderID="1" OrderDate="2023-01-01">
    <OrderDetails>
      <OrderDetail ProductID="101" Quantity="3" />
      <OrderDetail ProductID="102" Quantity="2" />
    </OrderDetails>
  </Order>
  <!-- More Order elements -->
</Orders>

there is a way to have OrderDetail as elements:

SELECT 
    O.OrderID AS '@OrderID',
    O.OrderDate AS '@OrderDate',
    (
        SELECT
            OD.ProductID AS 'ProductID',
            OD.Quantity AS 'Quantity'
        FROM OrderDetails OD
        WHERE OD.OrderID = O.OrderID
        FOR XML PATH('OrderDetail'), TYPE
    )
FROM Orders O
FOR XML PATH('Order'), ROOT('Orders')
<Orders>
  <Order OrderID="1" OrderDate="2023-01-01">
    <OrderDetail>
      <ProductID>101</ProductID>
      <Quantity>3</Quantity>
    </OrderDetail>
    <OrderDetail>
      <ProductID>102</ProductID>
      <Quantity>2</Quantity>
    </OrderDetail>
  </Order>
  <!-- More Order elements -->
</Orders>

No comments:

test smtp server with powershell

Send-MailMessage -SMTPServer smtp.domain.com -To [email protected] -From [email protected] -Subject "This is a test email" -Body ...