XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
search
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Home » Boards » Stylus Studio Developer Network » XQuery Help and Discussion » SQL Table To XML using XQuery
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
+ Stylus Studio Technical Forum (14621)
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
- XQuery Help and Discussion (2017)
-> + Multiple Sibling Matches (2)
-> + Issue with data($element) (4)
-> + convert text to xml from more ... (2)
-> + xquery syntax (2)
-> - SQL Table To XML using XQuery (4)
-> ->SQL Table To XML using XQ...
-> ->SQL Table To XML using XQ...
-> ->SQL Table To XML using XQ...
-> + delimiting values of attribute... (3)
-> + empty child nodes (9)
-> + Illegal XML characters (4)
-> + Complete SQL Server database t... (34)
-> + Prevent xquery from converting... (8)
-> + Another edi question. (6)
-> + Running xquery with a source c... (4)
-> + xml to edi segment count quest (3)
-> + Running xquery from the comman... (7)
-> + How to change the node name in... (5)
-> + How to loop through a set of e... (10)
-> + Can I pass a parameter/vaiable... (3)
-> + generating an XML file (3)
-> + generating log files of Xquery... (2)
-> + Stylus Studio crashes when edi... (3)
-- Previous [181-200] [201-220] [221-240] Next
+ Stylus Studio FAQs (159)
+ Stylus Studio Code Samples & Utilities (364)
+ Stylus Studio Announcements (113)
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>
   
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.