 next
|
  Subject: 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
|
|
next
|
  Subject: 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
|
|
next
|
Subject: 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
|
|
next
|
Subject: 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.
|
|
top