Specific XML layout needed

I have the following table:

CREATE TABLE PLMXC_ERP_INT_XML_BOM 
    (
     admi_num int, 
     stlh_num varchar(20),
     arti_oms_1 varchar(40),
     arti_code varchar(20),
     stlx_pos varchar(5),
     stlx_aantal numeric(16,6)
    );

with the following content:

INSERT INTO PLMXC_ERP_INT_XML_BOM
(admi_num, stlh_num, arti_oms_1, arti_code, stlx_pos, stlx_aantal)
VALUES
(1, '3P-2000245', 'Cover 2 undercolum 1MF', '3P-2000242', '1', 1),
(1, '3P-2000245', 'Cover 2 undercolum 1MF', '3P-2000243', '2', 1),
(1, '3P-2000245', 'Cover 2 undercolum 1MF', '3P-101707', '3', 5),
(1, '3P-101707', 'cover 1MF undercolumn', '3P-2000242', '1', 1),
(1, '3P-101707', 'cover 1MF undercolumn', '3P-2000243', '2', 1);

When I want to create an XML from this content I use

SELECT  stlh.admi_num
        ,stlh.stlh_num
        ,stlh.arti_oms_1 as stlh_oms_1
        ,(SELECT
            admi_num
            ,stlm.arti_code
            ,ROW_NUMBER()OVER(PARTITION BY stlm.stlh_num ORDER BY stlm.stlh_num) as stlm_num
            ,stlx_pos as stlm_pos
            ,stlx_aantal as stlm_aantal
            FROM PLMXC_ERP_INT_XML_BOM stlm
            WHERE stlm.stlh_num = stlh.stlh_num
            FOR XML AUTO, TYPE, ELEMENTS
        )
        FROM [PLMXC_ERP_INT_XML_BOM] stlh
        GROUP BY stlh.stlh_num, stlh.admi_num, stlh.arti_oms_1
        FOR XML AUTO, ELEMENTS

and this returns me

<stlh>
  <admi_num>1</admi_num>
  <stlh_num>3P-101707</stlh_num>
  <stlh_oms_1>cover 1MF undercolumn</stlh_oms_1>
  <stlm>
    <admi_num>1</admi_num>
    <arti_code>3P-2000242</arti_code>
    <stlm_num>1</stlm_num>
    <stlm_pos>1</stlm_pos>
    <stlm_aantal>1.000000</stlm_aantal>
  </stlm>
  <stlm>
    <admi_num>1</admi_num>
    <arti_code>3P-2000243</arti_code>
    <stlm_num>2</stlm_num>
    <stlm_pos>2</stlm_pos>
    <stlm_aantal>1.000000</stlm_aantal>
  </stlm>
</stlh>
<stlh>
  <admi_num>1</admi_num>
  <stlh_num>3P-2000245</stlh_num>
  <stlh_oms_1>Cover 2 undercolum 1MF</stlh_oms_1>
  <stlm>
    <admi_num>1</admi_num>
    <arti_code>3P-2000242</arti_code>
    <stlm_num>1</stlm_num>
    <stlm_pos>1</stlm_pos>
    <stlm_aantal>1.000000</stlm_aantal>
  </stlm>
  <stlm>
    <admi_num>1</admi_num>
    <arti_code>3P-2000243</arti_code>
    <stlm_num>2</stlm_num>
    <stlm_pos>2</stlm_pos>
    <stlm_aantal>1.000000</stlm_aantal>
  </stlm>
  <stlm>
    <admi_num>1</admi_num>
    <arti_code>3P-101707</arti_code>
    <stlm_num>3</stlm_num>
    <stlm_pos>3</stlm_pos>
    <stlm_aantal>5.000000</stlm_aantal>
  </stlm>
</stlh>

But what I need and I'm not able to return is

<stlh>
  <admi_num>1</admi_num>
  <stlh_num>3P-101707</stlh_num>
  <stlh_oms_1>cover 1MF undercolumn</stlh_oms_1>
</stlh>
<stlm>
  <admi_num>1</admi_num>
  <arti_code>3P-2000242</arti_code>
  <stlm_num>1</stlm_num>
  <stlm_pos>1</stlm_pos>
  <stlm_aantal>1.000000</stlm_aantal>
</stlm>
<stlm>
  <admi_num>1</admi_num>
  <arti_code>3P-2000243</arti_code>
  <stlm_num>2</stlm_num>
  <stlm_pos>2</stlm_pos>
  <stlm_aantal>1.000000</stlm_aantal>
</stlm>

<!-- split into multiple row (result) here -->

<stlh>
  <admi_num>1</admi_num>
  <stlh_num>3P-2000245</stlh_num>
  <stlh_oms_1>Cover 2 undercolum 1MF</stlh_oms_1>
</stlh>
<stlm>
  <admi_num>1</admi_num>
  <arti_code>3P-2000242</arti_code>
  <stlm_num>1</stlm_num>
  <stlm_pos>1</stlm_pos>
  <stlm_aantal>1.000000</stlm_aantal>
</stlm>
<stlm>
  <admi_num>1</admi_num>
  <arti_code>3P-2000243</arti_code>
  <stlm_num>2</stlm_num>
  <stlm_pos>2</stlm_pos>
  <stlm_aantal>1.000000</stlm_aantal>
</stlm>
<stlm>
  <admi_num>1</admi_num>
  <arti_code>3P-101707</arti_code>
  <stlm_num>3</stlm_num>
  <stlm_pos>3</stlm_pos>
  <stlm_aantal>5.000000</stlm_aantal>
</stlm>

I need to write this to seperate XML files, for each stlh_num a new file is needed.

Is this even possible?

Solution

select (
       select P1.admi_num,
              P1.stlh_num,
              P1.arti_oms_1
       for xml path('stlh'), type
       ),
       (
       select P2.admi_num,
              P2.arti_code,
              row_number() over(order by P2.stlh_num) as stlm_num,
              P2.stlx_pos as stlm_pos,
              P2.stlx_aantal as stl_aantal
       from PLMXC_ERP_INT_XML_BOM as P2
       where P1.admi_num = P2.admi_num and
             P1.stlh_num = P2.stlh_num and
             P1.arti_oms_1 = P2.arti_oms_1
       for xml path('stlm'), type
       )
from PLMXC_ERP_INT_XML_BOM as P1
group by P1.admi_num,
         P1.stlh_num,
         P1.arti_oms_1
for xml path('')

To split the result in different rows you can use this instead.

select (
       select (
              select P1.admi_num,
                     P1.stlh_num,
                     P1.arti_oms_1
              for xml path('stlh'), type
              ),
              (
              select P2.admi_num,
                     P2.arti_code,
                     row_number() over(order by P2.stlh_num) as stlm_num,
                     P2.stlx_pos as stlm_pos,
                     P2.stlx_aantal as stl_aantal
              from PLMXC_ERP_INT_XML_BOM as P2
              where P1.admi_num = P2.admi_num and
                    P1.stlh_num = P2.stlh_num and
                    P1.arti_oms_1 = P2.arti_oms_1
              for xml path('stlm'), type
              )
       for xml path(''), type
       )
from PLMXC_ERP_INT_XML_BOM as P1
group by P1.admi_num,
         P1.stlh_num,
         P1.arti_oms_1