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.


Comments

Post a Comment

Popular posts from this blog

powershell Start-Process exit code -1073741502 when used with Credential from a windows service environment -

i'm running strange behavior powershell start-process call. here call: $process = start-process ` "c:\somepath\mybinary.exe" ` -passthru ` -credential $defaultcredential ` -wait ` -workingdirectory "c:\somepath" ` -loaduserprofile if ($process.exitcode -ne 0) { #do } this call return exit code - 1073741502 . after quick search, exit code seems related generic error when program not load required dll (aka. status_dll_init_failed ). when run without -credential $credential program runs correctly. in order isolate problem, manually launched some.exe in prompt target credential , runs smoothly. so problem seems come way start-process cmdlet launch process. i found potential solutions problem tried apply no luck : link , link . would have idea of what's going on here ? edit 1: run proc mon monitoring program activities when launched directly or via powershell script. problem seems occur when loading kernelbase
Read more

twig - Using Twigbridge in a Laravel 5.1 Package -

i'm trying create laravel 5.1 package uses twigbridge instead of .php views. can normal .php views display fine, when try make use of .twig templates i'm met error. possible use twigbridge templates when creating views laravel package or pretty stuck blade , php ? edit 1: loading twig template without extending works. it's when start using {% extends 'file' %} error comes out. i error error loading /virtual/laravel/packages/username/mypackage/src/views/sample.twig: template "templates.master" not defined () in "/virtual/laravel/packages/username/mypackage/src/views/sample.twig" @ line 1. my folders username |_ mypackage |_ src |_ controllers - samplecontroller.php |_ views |_ templates - master.twig - sample.twig twig template sample.twig {% extends 'templates.master' %} {% block content %} {{ parent()
Read more

c# - LINQ join Entities from HashSet's, Join vs Dictionary vs HashSet performance -

i have hashset's each stores t, have written test application compares different relation algorithms can think of, i'm not pleased results getting. does there exist more efficient ways of achieving object relations, once testing? using system; using system.collections.generic; using system.linq; using system.text; using system.threading.tasks; using system.diagnostics; namespace linqtests { class program { static void main(string[] args) { hashset<user> usertable = new hashset<user>(); hashset<userproperty> userpropertytable = new hashset<userproperty>(); #region lets create dummy data. console.writeline("please wait while creating dummy data, can take while..."); console.writeline(""); int rows = 1000000; for(int x = 0; x < rows; x++) { random rnd = new random(); // ad
Read more