Generate XML file in Sql Server in a different way -

-

i have created 2 tables employer , reportpage.

the employerid common field 2 tables. want generate xml tables. write queries , execute.

the query here:

select e.employername employername,         e.employerid employerid, ( select       rp.employerid employerid,       rp.pagenumber pagenumber,        rp.title title,        rp.score score        reportpage rp  rp.employerid = e.employerid        xml auto, type, elements)        employer e employerid = 09340 xml auto, type, elements, root       ;

it gives output xml this:

<?xml version = '1.0' encoding = 'utf-8' standalone = 'no'?>     <root>  <e>     <employername>company, inc.</employername>     <employerid>09340</groupnumber>       <rp>           <employerid>09340</employerid>           <pagenumber>1</pagenumber>           <title>executive summary</title>               <score>67</score>       </rp>    </e>     <e>     <employername>company, inc.</employername>     <employerid>09340</groupnumber>       <rp>           <employerid>09340</employerid>           <pagenumber>2</pagenumber>           <title>executive summary</title>               <score>75</score>       </rp>    </e>      <e>     <employername>company, inc.</employername>     <employerid>09340</groupnumber>       <rp>           <employerid>09340</employerid>           <pagenumber>3</pagenumber>           <title>executive summary</title>               <score>80</score>       </rp>    </e>     </root>

but expected form of xml output:

<?xml version = '1.0' encoding = 'utf-8' standalone = 'no'?>     <root>        <e>           <employername>company, inc.</employername>           <employerid>09340</groupnumber>        </e>       <rp> <employerid>09340</employerid>           <pagenumber>1</pagenumber>           <title>executive summary</title>               <score>67</score>       </rp>            <rp> <employerid>09340</employerid>           <pagenumber>2</pagenumber>           <title>common</title>              <score>75</score>       </rp>            <rp> <employerid>09340</employerid>           <pagenumber>3</pagenumber>           <title>physical</title>                <score>80</score>       </rp>        </root>

could me that? have tried long time. thanks!

applying instructions article "belle’s sql musings: sqlxml: how join multiple xml snippets (using query() , union all)" on problem gave me code:

declare @employerid varchar(5) = '09340'  declare @equery xml declare @rpquery xml  set @equery = (   select     e.employername employername,      e.employerid employerid       employer e       employerid = @employerid   xml auto, type, elements, root )  set @rpquery = (   select     rp.pagenumber pagenumber,      rp.title title,      rp.score score       reportpage rp       rp.employerid = @employerid   order     rp.pagenumber   xml auto, type, elements, root )  select   @equery.query('//e') union select   @rpquery.query('//rp') xml path (''), root('root'), type

you can check results online in sql fiddle: http://sqlfiddle.com/#!6/e95b5/7/0

disclaimer: i'm no t-sql expert, query might improved bit , magic keywords thrown away, hacked using google without knowing (and why)


Comments

Post a Comment

Popular posts from this blog

java - Plugin org.apache.maven.plugins:maven-install-plugin:2.4 or one of its dependencies could not be resolved -

-
i getting error message , further 1 here: failed read artifact descriptor org.apache.maven.plugins:maven-install-plugin:jar:2.4: even though when go .m2 repository path c:\users\a591024.m2\repository\org\apache\maven\plugins\maven-resources-plugin\2.4 the maven-resources-plugin-2.4.jar file there , maven-resources-plugin-2.4.pom there. have tried delete .m2 repository no luck. far maven not build project desperately need to. any ideas? it seems proxy issue please aad following in setting.xml grabbing lan setting , proxy information. <proxies> <proxy> <id>proxy</id> <active>true</active> <protocol>http</protocol> <host>hostname</host> <port>port</port> </proxy> </proxies> this frequent issue, please see below link well. plugin org.apache.maven.plugins:maven-clean-plugin:2.5 or 1 of dependencies not resolved maven: failed ret...
Read more

Round ImageView Android -

-
Image
i working on android application in doing rounded image view. working fine images, images 160x120 resolution shows oval shaped. code custom imageview given below, please me out here: public class roundimage extends drawable { private final bitmap mbitmap; private final paint mpaint; private final rectf mrectf; private final int mbitmapwidth; private final int mbitmapheight; public roundimage(bitmap bitmap) { mbitmap = bitmap; mrectf = new rectf(); mpaint = new paint(); mpaint.setantialias(true); mpaint.setdither(true); final bitmapshader shader = new bitmapshader(bitmap, shader.tilemode.clamp, shader.tilemode.clamp); mpaint.setshader(shader); mbitmapwidth = mbitmap.getwidth(); mbitmapheight = mbitmap.getheight(); } @override public void draw(canvas canvas) { canvas.drawoval(mrectf, mpaint); } @override protected void onboundschange(rect bound...
Read more

How can I utilize Yahoo Weather API in android -

-
Image
i want develop app using yahoo weather api android. want current , forecast weather details. i have tried different sources (e.g.: https://github.com/survivingwithandroid/weatherlib , getting error weatherlib api android , unable execute activity in android weatherlib ) , getting different errors m unable resplve. there can proper tutorial or guide! error: -17 08:50:48.961 3168-3168/com.survivingwithandroid.weather e/androidruntime﹕ fatal exception: main process: com.survivingwithandroid.weather, pid: 3168 java.lang.runtimeexception: unable start activity componentinfo{com.survivingwithandroid.weather/com.survivingwithandroid.weather.mainactivity}: java.lang.nullpointerexception: attempt invoke interface method 'boolean java.util.list.isempty()' on null object reference @ android.app.activitythread.performlaunchactivity(activitythread.java:2298) @ android.app.activitythread.handlelaunchactivity(activitythread.java:2360) @ android.app.activi...
Read more