Writing SQL to develop XML with children -

i'm trying write sql generate xml string complete child elements. can everything, children aren't falling children, showing separate root level items. i've been dinking around xml path statement, not having luck.

help appreciated. i'm on sql 2014 server.

declare @xmldata xml set     @xmldata = (    select adr.account_identifier                                , adr.first_name                             , adr.last_name                             , adr.addr_line1                             , adr.addr_line2                             , adr.city                             , adr.state                             , adr.zip                             , ( select acct_nbr                                     , loan_orgl_prin_amt                                     , loan_ostd_prin_amt                                     , loan_eftv_int_rt                                  staging.statementofbalance_src sob                                  sob.acct_nbr = crbr.acct_nbr                                  xml path('accounts'), type, elements )                         staging.account_data crbr                         join unit_cost.addresses_all adr on crbr.account_identifier = adr.account_identifier                             xml path('letter'), root ('statement'), elements    )  select @xmldata returnxml 

my results

<statement>     <letter>         <account_identifier>123321</account_identifier>         <first_name>pippi</first_name>         <last_name>longstockings</last_name>         <addr_line1>123 out onna boat</addr_line1>         <addr_line2 />         <city>ocean</city>         <state>me</state>         <zip>000000</zip>         <accounts>             <acct_nbr>0000000000000000</acct_nbr>             <loan_orgl_prin_amt>2670.00</loan_orgl_prin_amt>             <loan_ostd_prin_amt>2749.09</loan_ostd_prin_amt>             <loan_eftv_int_rt>4.75000</loan_eftv_int_rt>         </accounts>     </letter>     <letter>         <account_identifier>123321</account_identifier>         <first_name>pippi</first_name>         <last_name>longstockings</last_name>         <addr_line1>123 out onna boat</addr_line1>         <addr_line2 />         <city>ocean</city>         <state>me</state>         <zip>000000</zip>         <accounts>             <acct_nbr>0000000000000000</acct_nbr>             <loan_orgl_prin_amt>4082.00</loan_orgl_prin_amt>             <loan_ostd_prin_amt>5520.21</loan_ostd_prin_amt>             <loan_eftv_int_rt>5.50000</loan_eftv_int_rt>         </accounts>     </letter> </statement> 

but need is:

 <statement>         <letter>             <account_identifier>123321</account_identifier>             <first_name>pippi</first_name>             <last_name>longstockings</last_name>             <addr_line1>123 out onna boat</addr_line1>             <addr_line2 />             <city>ocean</city>             <state>me</state>             <zip>000000</zip>             <accounts>                 <acct_nbr>0000000000000000</acct_nbr>                 <loan_orgl_prin_amt>2670.00</loan_orgl_prin_amt>                 <loan_ostd_prin_amt>2749.09</loan_ostd_prin_amt>                 <loan_eftv_int_rt>4.75000</loan_eftv_int_rt>             </accounts>             <accounts>                 <acct_nbr>0000000000000000</acct_nbr>                 <loan_orgl_prin_amt>4082.00</loan_orgl_prin_amt>                 <loan_ostd_prin_amt>5520.21</loan_ostd_prin_amt>                 <loan_eftv_int_rt>5.50000</loan_eftv_int_rt>             </accounts>         </letter>     </statement> 

i think, problem join in outer select... did not provide structure, nor did provide sample data...

your actual output doubles master row while want - @ least expected output points - 1:n relation between letter , account.

i tried simplify query , declared tables fitting query, took away joined table , output seems ok:

declare @letter table(account_identifier int,first_name varchar(100),last_name varchar(100)); insert @letter values(123321,'pippi','longstockings'); declare @account table(account_identifier int,acct_nbr varchar(100), loan_orgl_prin_amt decimal (14,4)); insert @account values(123321,'00000000000',2670.00),(123321,'00000000000',4082.00);  select        adr.account_identifier        , adr.first_name     , adr.last_name     --, adr.addr_line1     --, adr.addr_line2     --, adr.city     --, adr.state     --, adr.zip     , ( select acct_nbr             , loan_orgl_prin_amt             --, loan_ostd_prin_amt             --, loan_eftv_int_rt          @account sob -- staging.statementofbalance_src sob          sob.account_identifier = adr.account_identifier-- crbr.acct_nbr          xml path('accounts'), type, elements ) @letter adr --staging.account_data crbr                  --join unit_cost.addresses_all adr on crbr.account_identifier = adr.account_identifier     xml path('letter'), root ('statement')  

the result

<statement>   <letter>     <account_identifier>123321</account_identifier>     <first_name>pippi</first_name>     <last_name>longstockings</last_name>     <accounts>       <acct_nbr>00000000000</acct_nbr>       <loan_orgl_prin_amt>2670.0000</loan_orgl_prin_amt>     </accounts>     <accounts>       <acct_nbr>00000000000</acct_nbr>       <loan_orgl_prin_amt>4082.0000</loan_orgl_prin_amt>     </accounts>   </letter> </statement> 

so advise:

test without for xml path , go outside inside. first should happy

<statement>   <letter>     <account_identifier>123321</account_identifier>     <first_name>pippi</first_name>     <last_name>longstockings</last_name>     --more columns   </letter> </statement> 

when works, define sub-select add appropriate accounts. query looks quite ok acutally, suspect reason in table's relations not result way expect it...