XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Jerry BeechSubject: 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

Postnext
Minollo I.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>

Postnext
Jerry BeechSubject: 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.

Posttop
Minollo I.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>

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.