sql server - SQL Data as XML Element -

i new on generating xml out sql. trying generate xml query:

select rpttype, datatype, branch, acc, actual  reportingtb 

the query produces following results:

rpttype   datatype   branch    acc    actual -------   --------   -------   ----   ----------- mtd       ups        arizona   total  2279.00000 mtd       ups        arizona   oral   543.00000 mtd       ups        arizona   tube   532.00000 mtd       ups        arizona   other  1.00000 

i want use query results in first 4 columns actual xml elements this:

<mtd>   <ups>     <arizona>       <total>         <actual>2279.00000</actual>       </total>       <oral>         <actual>543.00000</actual>       </oral>       <tube>         <actual>532.00000</actual>       </tube>       <other>         <actual>1.00000</actual>       </other>     </arizona>   <ups> </mtd> 

can point me in right direction on how this? should maybe use t-sql , loop through results of query in order make xml file?

thanks help!

it's technically possible, it's bad few reasons. instead, should rethink xml structure, more this:

<root>   <reportingtb rpttype="mtd" datatype="ups" branch="arizona">     <actual acc="total">2279.00000</actual>     <actual acc="oral">543.00000</actual>     <actual acc="tube">532.00000</actual>     <actual acc="other">1.00000</actual>   </reportingtb> </root> 

using query:

declare @t table (rpttype varchar(20),   datatype varchar(20),   branch varchar(20),    acc varchar(20),   actual numeric(10,5)); insert @t values ('mtd',  'ups'        ,'arizona',   'total',  2279.00000) ,('mtd',  'ups'        ,'arizona',   'oral',  543.00000) ,('mtd',  'ups'        ,'arizona',   'tube',  532.00000) ,('mtd',  'ups'        ,'arizona',   'other',  1.00000);  select rpttype '@rpttype'     ,datatype '@datatype'     ,branch '@branch'     ,(select           acc '@acc'         ,actual '*'     @t t2     t2.rpttype = t1.rpttype , t2.datatype = t1.datatype , t2.branch = t1.branch     xml path('actual'), type) @t t1 group rpttype, datatype, branch xml path('reportingtb'), root('root'); 

this gives xml can sensibly validated against schema (instead of schema has contain many possible node names). handle different grouping possibilities more naturally, , take care of xml weirdness for xml handles behind scenes.

that said, technically possible achieve original desire; here's query it:

select      cast('<' + rpttype + '>'     + (select          '<' + datatype + '>'          + (select             '<' + branch + '>'             + replace(replace(             (select                  '#' + acc + '!' '*' , actual, '#/' + acc + '!' '*'                 @t t4 t4.branch = t3.branch , t4.datatype = t2.datatype , t4.rpttype = t1.rpttype                 xml path('')), '#', '<'), '!', '>')              + '</' + branch + '>'              @t t3  t3.datatype = t2.datatype , t3.rpttype = t1.rpttype group branch)         + '</' + datatype + '>'         @t t2  t2.rpttype = t1.rpttype group datatype)     + '</' + rpttype + '>'  xml) @t t1 group rpttype 

note should not this, it's ugly hackery (i'm showing give idea of how ugly part of way there). gets worse. i'm using weird string replaces may or may not work in wild. on top of that, if node has invalid xml character, you'd have escape - maybe throw in additional replaces (replace(col, '<', '&lt;')) or something, again that's ugly , has repeated on several values. you're using sql server xml writer, , it's not meant that. if absolutely must have structure, should pass data clr class can use xmlwriter or xdocument write actual xml using these values. put clr class in sql server , call stored procedure.