|
next
|
Subject: SQL Table To XML using XQuery Author: Jerry Beech Date: 15 Aug 2008 03:22 PM
|
I hate to say Newbie, but I already did so now you know.
Im trying to pull records from a single SQL Table into a an xml file
The view has these fields:
ResourceName, ClassName, ScreenName, ClassSortOrder
Data would be like so:
AAA,EEE,DDD,2
AAA,BBB,CCC,1
AAA,BBB,CCC,2
AAA,BBB,DDD,1
AAA,EEE,DDD,3
AAA,BBB,DDD,2
I cant seem to pull the data with xQuery so that it does not repeat the ResourceName every time.
Here is an example of what I expected to get back
<?xml version="1.0"?>
<ROOT>
<Resource>
<ResourceID>AAA</ResourceID>
<Class>
<ClassName>BBB</ClassName>
<ScrRptRecord ScrRptName="CCC" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1643">
<ClassSortOrder>1</ClassSortOrder>
</ScrRptRecord>
<ScrRptRecord ScrRptName="CCC" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1644">
<ClassSortOrder>2</ClassSortOrder>
</ScrRptRecord>
<ScrRptRecord ScrRptName="DDD" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1644">
<ClassSortOrder>1</ClassSortOrder>
</ScrRptRecord>
<ScrRptRecord ScrRptName="DDD" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1644">
<ClassSortOrder>2</ClassSortOrder>
</ScrRptRecord>
</Class>
<Class>
<ClassName>EEE</ClassName>
<ScrRptRecord ScrRptName="CCC" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1643">
<ClassSortOrder>1</ClassSortOrder>
</ScrRptRecord>
<ScrRptRecord ScrRptName="DDD" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1643">
<ClassSortOrder>1</ClassSortOrder>
</ScrRptRecord>
<ScrRptRecord ScrRptName="DDD" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1644">
<ClassSortOrder>2</ClassSortOrder>
</ScrRptRecord>
<ScrRptRecord ScrRptName="DDD" PROC-Version="2" QueryDate="2008-08-13T13:01:39.803" TableID="13" ScrRptID="1643">
<ClassSortOrder>3</ClassSortOrder>
</ScrRptRecord>
</Class>
</Resource>
</ROOT>
Ok, let me have it -- how dumb am I?
Jerry
|
next
|
Subject: SQL Table To XML using XQuery Author: Jerry Beech Date: 15 Aug 2008 07:42 PM
|
Grouping seems to be the key but ClassSortOrder, along with several other fields need to be returned for each ScrRptRecord.
It looks like this as we currently have it hand written
<ROOT>
<Resource>
<ResourceID/>
<Class>
<ClassName/>
<ScrRptRecord ScrRptName="">
<Field1/>
<Field2/>
<Field3/>
<Field4/>
<Filed5/>
</ScrRptRecord>
</Class>
</Resource>
</ROOT>
Field 1 thru 4, along with the ClassSortOrder are contained in the same row.
So, in reality the table has these fields
ResourceName, ClassName, ScreenName, ClassSortOrder,Field1,Field2,Field3,Field4,Field5
The object of all this is to return to me the order in wich I put fields on the screen for different data entry screens.
|
|
|
|