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
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
dean katzSubject: How to create SQL/XML
Author: dean katz
Date: 03 Aug 2005 06:17 PM
I am very new to XML, XSD as well as stylus studio. I have a table that was created using MSAccess 2002. This table is already upsized to our SQL server. I now have to transfer the contents of this table to an outside party but before I do that they want me to validate the records in the table against the schema created by them. Some of the fields in my table is defined as elements in the schema but there a few which are defined as attributes. How do I even begin to aproach the task of transfering from a table format to XML format that matches the consultant's schema?

sample:
table 'clients' has these fields
record_id
add_date
edit_date
lname
fname
gender
dob

according to the schema record_id, add_date and edit_date are attributes
whereas lname, fname, gender and dob are elements.

how do I create the SQL/XML that will get record_id, add_date and edit_date as attributes and lname, fname, gender and dob as elements? please help.

Postnext
Tony LavinioSubject: How to create SQL/XML
Author: Tony Lavinio
Date: 03 Aug 2005 10:58 PM
Here is a step-by-step video tutorial on how to do exactly that. http://www.stylusstudio.com/videos/dbtoxml1/dbtoxml1.html

Postnext
dean katzSubject: How to create SQL/XML
Author: dean katz
Date: 04 Aug 2005 11:21 AM
Thank you for the pointer. I started on that page to create an SQL/XML query below. But somehow kept getting an error message. What is wrong with my SELECT statement? Thank you in advance for your help.

SELECT
XMLELEMENT(name "row",
XMLATTRIBUTES("record_id",t.record_id),
XMLATTRIBUTES("system_id",t.system_id),
XMLATTRIBUTES("odbid",t.odbid),
XMLATTRIBUTES("date_added",t.date_added),
XMLATTRIBUTES("date_updated",t.date_updated),
XMLELEMENT(name "first_name",t.first_name),
XMLELEMENT(name "last_name",t.last_name),
XMLELEMENT(name "mi_initial",t.mi_initial),
XMLELEMENT(name "soc_sec_no",t.soc_sec_no),
XMLELEMENT(name "ethnic",t.ethnic),
XMLELEMENT(name "gender",t.gender),
XMLELEMENT(name "dob",t.dob)
)
FROM SPSQL.dbo.clients t

Postnext
Ivan PedruzziSubject: How to create SQL/XML
Author: Ivan Pedruzzi
Date: 04 Aug 2005 12:03 PM
XMLATTRIBUTES has a different syntax from XMLELEMENT. Change your query to

SELECT
XMLELEMENT(name "row",
XMLATTRIBUTES(t.record_id, t.system_id, t.odbid, t.date_added, t.date_updated),
XMLELEMENT(name "first_name",t.first_name),
XMLELEMENT(name "last_name",t.last_name),
XMLELEMENT(name "mi_initial",t.mi_initial),
XMLELEMENT(name "soc_sec_no",t.soc_sec_no),
XMLELEMENT(name "ethnic",t.ethnic),
XMLELEMENT(name "gender",t.gender),
XMLELEMENT(name "dob",t.dob)
)
FROM SPSQL.dbo.clients t


Hope this helps
Ivan Pedruzzi
Stylus Studio Team

Postnext
dean katzSubject: How to create SQL/XML
Author: dean katz
Date: 04 Aug 2005 12:10 PM
aaaaah. i c. thank you!

Postnext
dean katzSubject: How to create SQL/XML
Author: dean katz
Date: 24 Aug 2005 06:08 PM
Originally Posted: 04 Aug 2005 02:12 PM
Here's more question...<br> <br> The schema that am using to validate my XML document have an element called dynamic_content. This element holds info about gender, birthdate, weight, height etc. I don't have a field in my table that corresponds to this dynamic_content element. But I have fields on my table that carries info on gender and birthdate. How should I change the SQL/XML syntax to include the gender and birthdate field and still stick to the schema? GOd, I really hope I am making sense. Felt like I am learning to swim as I was drowning!

Postnext
Ivan PedruzziSubject: How to create SQL/XML
Author: Ivan Pedruzzi
Date: 04 Aug 2005 06:30 PM
Create the dynamic_content elemen and then bind the columns you like as children


SELECT
XMLELEMENT(name "row",
XMLATTRIBUTES(t.record_id, t.system_id, t.odbid, t.date_added, t.date_updated),
XMLELEMENT(name "first_name",t.first_name),
XMLELEMENT(name "last_name",t.last_name),
XMLELEMENT(name "mi_initial",t.mi_initial),
XMLELEMENT(name "soc_sec_no",t.soc_sec_no),
XMLELEMENT(name "ethnic",t.ethnic),

XMLELEMENT(name "dynamic_content"
XMLELEMENT(name "gender",t.gender),
XMLELEMENT(name "birthdate",t.birthdate)),

XMLELEMENT(name "dob",t.dob)
)
FROM SPSQL.dbo.clients t

Postnext
dean katzSubject: How to create SQL/XML
Author: dean katz
Date: 24 Aug 2005 06:07 PM
another question:
i have a table 'clubs' with these fields: club_id, clubname, clubtype, yearform.

i also have another table that list the members of each club. members table have these fields: member_id, member_name, date_join, member_rank, and member_position. in the schema, members is listed as element of clubs. how would I craft the SQL/XML ststement to include all element inside clubs and members. is it possible to create a SELECT statement within another Select statement like in t-sql like this:

SELECT
XMLELEMENT(name "clubs,
XMLELEMENT(name "club_id", t.club_id),
XMLELEMENT(name "club_name", t.club_name),
SELECT
XMLELEMENT(name "members",
XMLELEMENT(name "member_name", t.member_name),
XMLELEMENT(name "member_id", t.memberid),
XMLELEMENT(name "date_join", t.date_join)
))
FROM ClubDB.dbo.tblmembers

)
FROM ClubDB.dbo.tblclubs

I get an incorrect syntax when I try to run this. Please help.

Posttop
Ivan PedruzziSubject: How to create SQL/XML
Author: Ivan Pedruzzi
Date: 24 Aug 2005 11:01 PM
The question has been already answered, see the following post

http://www.stylusstudio.com/SSDN/default.asp?action=9&read=3742&fid=46#10720


Ivan Pedruzzi
Stylus Studio Team

 
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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.