I am trying to generate an XML string based on data in two SQL tables. One contains Order Header data, the other one Line Item data.

My problem is that I can't get the Line Item info to appear properly as multiple elements within a single order:

This is the SQL statement:

Select 

    LTRIM(RTRIM(H.CustPONbr)) As "Transactions/Transaction/CustomerOrdNumber",             

    (

    Select LTRIM(RTRIM(InvtID)) As "data()" From X888_Amazon_Order_Line L1

    Where L1.CpnyID = H.CpnyID And L1.CustPONbr = H.CustPONbr

    FOR XML PATH (''), ELEMENTS

    ) As "Transactions/Transaction/LineItems/LineItem/InvtId"

From X888_Amazon_Order_Header H (nolock)

 where h.CustPONbr = '99999014'

For XML PATH ('ProcessEngineSubmission'), Root ('XML'), ELEMENTS

This is the result I get:

<XML>

    <Transactions>

      <Transaction>

        <CustomerOrdNumber>99999014</CustomerOrdNumber>

        <LineItems>

          <LineItem>

            <InvtId>TEST 1235 TEST 1234</InvtId>

          </LineItem>

        </LineItems>

      </Transaction>

    </Transactions>

</XML>

If I execute the inner select (replacing "data()" with InvtId), I get what I am trying to achieve:

<InvtId>TEST 1235</InvtId>

<InvtId>TEST 1234</InvtId>

I posted about this on Stackoverflow.com and got the solution there - http://stackoverflow.com/questions/35720120/how-to-generate-xml-using-sql-path-mode-with-line-items

"Nested selects need the ,TYPE extension to come back as XML"

Select 

    LTRIM(RTRIM(H.CustPONbr)) As "Transactions/Transaction/CustomerOrdNumber",             

    (

    Select LTRIM(RTRIM(InvtID)) As "data()" From X888_Amazon_Order_Line L1

    Where L1.CpnyID = H.CpnyID And L1.CustPONbr = H.CustPONbr

    FOR XML PATH (''), TYPE

    ) As "Transactions/Transaction/LineItems/LineItem/InvtId"

From X888_Amazon_Order_Header H (nolock)

 where h.CustPONbr = '99999014'

For XML PATH ('ProcessEngineSubmission'), Root ('XML'), ELEMENTS