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>
Subject:SQL Table To XML using XQuery Author:Minollo I. Date:15 Aug 2008 03:55 PM
I'm not sure I understand the role of ClassSortOrder; I would imagine it's used to decide the order in which to scan ClassNames, but then I don't understand how the same ClassName can be associated to different ClassSortOrder values... So, I'm missing something.
Anyway, looks like a grouping question; this could be a start:
<ROOT> {
for $resource in distinct-values(collection("Books.dbo.test")/test/ResourceName)
return
<Resource>
<ResourceID>{$resource}</ResourceID>
{
for $class in distinct-values(collection("Books.dbo.test")/test[ResourceName=$resource]/ClassName)
let $screens := collection("Books.dbo.test")/test[ResourceName=$resource and ClassName=$class]
order by $screens[1]/ClassSortOrder
return
<Class>
<ClassName>{$class}</ClassName>
{
for $screen in $screens/ScreenName
return
<ScrRptRecord ScrRptName="{string($screen)}"/>
}
</Class>
}
</Resource>
} </ROOT>
Subject:SQL Table To XML using XQuery Author:Minollo I. Date:18 Aug 2008 11:51 AM
...the concept is equivalent:
<ROOT> {
for $resource in distinct-values(collection("Books.dbo.test")/test/ResourceName)
return
<Resource>
<ResourceID>{$resource}</ResourceID>
{
for $class in distinct-values(collection("Books.dbo.test")/test[ResourceName=$resource]/ClassName)
return
<Class>
<ClassName>{$class}</ClassName>
{
for $row in collection("Books.dbo.test")/test[ResourceName=$resource and ClassName=$class]
return
<ScrRptRecord ScrRptName="{$row/ScreenName}">
<Field1>{$row/field1/text()}</Field1>
<Field2>{$row/field2/text()}</Field2>
</ScrRptRecord>
}
</Class>
}
</Resource>
} </ROOT>