SQL Inserting data in master table and then reference data in detail table

I have two table

MasterReg(MasterID,Revenue,Date,Desc) MasterID Identity field

DetailReg(DetailID,MasterID,NumValue) DetailID Identity field

I am trying to insert data from xmlstring using openxml

insert into MasterReg (Revenue,Date,Desc) Select Revenue,Date,Desc From OPENXML(....

this will insert 5 rows in my MasterReg table

DetailReg table contain 6 row for each of value inserted in MasterReg table ex... for MasterID=1 there will be Six DetailID in DetailReg table(Six Entry in DetailReg for one DetailReg Entry)

My XMl String is

<Root>
<Detail>
<Revenue>333300</Revenue>
<Date>21/6/2011</Date>
<Desc>desc text...</Desc>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
</Detail>
<Detail>
<Revenue>333300</Revenue>
<Date>21/6/2011</Date>
<Desc>desc text...</Desc>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
</Detail>
<Detail>
<Revenue>333300</Revenue>
<Date>21/6/2011</Date>
<Desc>desc text...</Desc>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
<NumValue value="56"/>
</Detail>
</Root>

I dont want to use cursor for this one...The first MasterReg entry done Successfully but i doesn't have idea about how to insert in DetailReg associated with master reg table... Both table at initial have no data in it..

Read more ...

Sort data based on value in a node

I can't seem to find any solutions on here already to this problem, even though I'm sure there are plenty.

I'm pulling data from a web service (name, date, email, company, subtotal), with subtotal being the most important for this problem. Right now it sorts based on the date the order was placed, but I need it to sort by subtotal (highest to lowest).

I can't make any changes to the xml or web service itself.

Read more ...