June 30, 2010 at 11:32 am
I'm really new to xml programming via sql, so please pardon my ignorance. I'm just stumped at this moment and haven't been able to make any progress at all. For some this may seem like a minute task, but for me, since I'm such a beginner, it has been a very challenging task. So pointing me in a right direction would be greatly appreciated.
I have multiple SQL queries, that need to be combined and spit out as part of single XML file, however following the below strickt format. Each element represents a specific query. For example, "<vendor", "<company", "<courses".......all represent their own queries. I have looked into FOR XML PATH and FOR XML EXPLICIT as my possible solutions perhaps, but just haven't been able to nail down the exact format. Again, I really appreciate the help. Thanks.
<import date="6/30/2010 9:02" src="1234">
- <vendors>
<vendor xid="V1" name="Corporate University" internal="true" />
<vendor xid="V2" name="Learning Provider" internal="false" />
</vendors>
- <companies>
<company xid="CO1" name="ABC Corporation" />
</companies>
- <courses>
<course xid="C3" name="Basic Training Course" desc="A course on basic training." ol="false" />
<course xid="C4" name="Basic Training Course Web Based" desc="An online course on basic training." ol="true" />
</courses>
</import>
June 30, 2010 at 12:34 pm
Can you provide us with the DDL for the tables involved along with some sample data? See the link in my signature line for posting sample data.
June 30, 2010 at 12:51 pm
In the meantime, consider this example:
DECLARE @Vendors TABLE (xid varchar(2), name varchar(20), internal bit)
INSERT INTO @Vendors
SELECT 'V1', 'Corporate University', 1 UNION ALL
SELECT 'V2', 'Learning Provider', 0
DECLARE @Companies TABLE (xid varchar(3), name varchar(50))
INSERT INTO @Companies
SELECT 'CO1', 'ABC Corporation'
DECLARE @Courses TABLE (xid varchar(2), name varchar(50), [desc] varchar(150), ol bit)
INSERT INTO @Courses
SELECT 'C3', 'Basic Training Course', 'A course on basic training.', 0 UNION ALL
SELECT 'C4', 'Basic Training Course Web Based', 'An online course on basic training.', 1
DECLARE @VendorsXML xml,
@CompaniesXML xml,
@CoursesXML xml
SELECT @VendorsXML = (SELECT * FROM @vendors vendor FOR XML AUTO, ROOT('vendors')),
@CompaniesXML = (SELECT * FROM@companies comany FOR XML AUTO, ROOT('companies')),
@CoursesXML = (SELECT * FROM @Courses course FOR XML AUTO, ROOT('courses'))
SELECT (SELECT @VendorsXML TYPE),
(SELECT @CompaniesXML TYPE),
(SELECT @CoursesXML TYPE)
FOR XML RAW(''), ELEMENTS, ROOT('import')
June 30, 2010 at 3:16 pm
John, you made this look so easy! 🙂
This is exactly what I was looking for. I'm gonna go ahead and apply this solution and will share my results. Thanks again!
July 1, 2010 at 5:57 pm
Hi folks,
So I was able to successfully produce the file exactly how I wanted, but except for one minor (ironically, has been a big headache) problem. I'm unable to determine how to add attributes to my root element of <import>
I would like it to look something like this:
<import date="5/21/2008 9:02" src="1234">
<child nodes> </child nodes>
</import>
Here is my sql code:
DECLARE @Courses TABLE (xid varchar(9), name varchar(250), [desc] varchar(150))
INSERT INTO @Courses
SELECT DISTINCT
xid = CASE
WHEN PrgType IN (1,3) THEN 'USA'
WHEN PrgType IN (2,4) THEN PC.ClientCode + '-' + SUBSTRING(E.EventID,5,3)
WHEN LEFT(S.SubEventID,1) = 'D' then '-' + 'OL' ELSE '' END,
RTRIM(E.name),
NULL AS [desc]
FROM Event E
JOIN S ON E.EventID = S.EventID
LEFT JOIN PC ON LEFT(E.EventID,3) = PC.ClientCode
WHERE S.Begin_Date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()+7), 0)
DECLARE @Classes TABLE (xid VARCHAR(20),sd VARCHAR(20), ed VARCHAR(20), xloc VARCHAR(20), xcourse VARCHAR(10), lm VARCHAR(5), headcount VARCHAR(5), xvendor VARCHAR(5), survey VARCHAR(20), classname VARCHAR (500))
INSERT INTO @Classes
SELECT DISTINCT
RTRIM(S.EventID) + '/' + RTRIM(S.SubEventID) AS xid,
CONVERT(VARCHAR(10),S.Begin_Date,103) AS sd ,
CONVERT(VARCHAR(10),S.End_Date ,103)AS ed,
LEFT(S.FacilityID,9) AS xloc,
PC.GlobalCode + '-' + SUBSTRING(E.EventID,5,3) AS xcourse,
lm = CASE
WHEN LEFT(S.SubEventID,1) IN ('a','x') THEN 1
WHEN LEFT(S.SubEventID,1) = 'D' THEN 2
WHEN LEFT(S.SubEventID,1) IN ('b','v') THEN 4
END,
S.reg_cnt AS headcount,
xvendor = CASE
WHEN PC.PrgType = 4 THEN PC.ClienTCode ELSE 'ESI' END,
survey = CASE
WHEN PC.PrgType IN (1,3) THEN 'true'
WHEN PC.PrgType IN (2,4) THEN 'no email'
ELSE 'true' END,
classname = CASE
WHEN ISNULL(S.Cust_Name,'') <> '' THEN RTRIM(Cust_Name) ELSE RTRIM(E.Name) END + ', ' + CONVERT(VARCHAR,S.Begin_Date)
FROM Event E
JOIN S ON E.EventID = S.EventID
LEFT JOIN PC ON LEFT(E.EventID,3) = PC.ClientCode
WHERE S.Begin_Date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()+7), 0)
DECLARE @Instructors TABLE(xid VARCHAR(20), fname VARCHAR(25), lname VARCHAR(25), email VARCHAR(50))
INSERT INTO @Instructors
SELECT DISTINCT
RTRIM(I.ID) AS xid,
N.fname,
N.lname,
N.email
FROM S
JOIN [datetime] SC ON S.EventID = SC.EventID AND S.SubEventID = SC.SubEventID
JOIN I ON SC.SchedlID = I.SchedlID
LEFT JOIN ESINet.dbo.tbl P ON RTRIM(I.ID) = P.ID
JOIN N ON P.NameID = N.NameID
WHERE S.Begin_Date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()+7), 0)
DECLARE @Students TABLE (xid VARCHAR(20), email VARCHAR(50), VARCHAR(20), jt VARCHAR(20), x VARCHAR(25), firstname VARCHAR(100), lastname VARCHAR(100),title VARCHAR(250), cancel VARCHAR(5))
INSERT INTO @Students
SELECT DISTINCT
RTRIM(P.ID) AS xid,
RTRIM(P.email),
= CASE WHEN LEFT(S.EventID,1) IN ('a','b','d') THEN C.Name ELSE NULL END,
NULL AS jt,
x = CASE WHEN LEFT(S.EventID,1) IN ('x','l','v') THEN PC.GlobalCode + '-' + CAST(PC.ID AS VARCHAR) ELSE NULL END,
RTRIM(P.firstname),
RTRIM(P.lastname),
RTRIM(P.JobTitle) AS title,
cancel = CASE WHEN R.Status > 1 THEN 'TRUE' ELSE 'FALSE' END
FROM S
JOIN R ON S.EventID = R.EventID AND S.SubEventID = R.SubEventID
JOIN P ON R.ID = P.ID
LEFT JOIN C ON P.ID = C.ID
LEFT JOIN PC ON ISNULL(S.IntlClient,LEFT(S.EventID,3)) = ISNULL(PC.ClientCode,PC.GlobalCode)
WHERE S.Begin_Date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()+7), 0)
DECLARE @Companies TABLE (xid VARCHAR(20), name VARCHAR(250))
INSERT INTO @Companies
SELECT DISTINCT
PC.GlobalCode + '-' + CAST(PC.ID AS VARCHAR) AS xid,
PC.ClientName AS name
FROM S
JOIN R ON S.EventID = R.EventID AND S.SubEventID = R.SubEventID
JOIN P ON R.ID = P.ID
JOIN PC ON ISNULL(S.IntlClient,LEFT(S.EventID,3)) = ISNULL(PC.ClientCode,PC.GlobalCode)
WHERE S.Begin_Date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()+7), 0)
--AND LEFT(S.EventID,1) IN ('x','l','v') **uncomment that after done running xml query
DECLARE @ClassInstructors TABLE (xid VARCHAR(20))
INSERT INTO @ClassInstructors
SELECT DISTINCT
RTRIM(I.ID) AS xid
FROM S
JOIN I SC ON S.EventID = SC.EventID AND S.SubEventID = SC.SubEventID
JOIN I ON SC.SchedlID = I.SchedlID
LEFT JOIN ESINet.dbo.tbl P ON RTRIM(I.ID) = P.ID
JOIN N ON P.NameID = N.NameID
WHERE S.Begin_Date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()+7), 0)
DECLARE @CoursesXML XML,
@ClassesXML XML,
@InstructorsXML XML,
@StudentsXML XML,
@CompaniesXML XML,
@ClassInstructorsXML XML
SELECT @CoursesXML = (SELECT * FROM @Courses course FOR XML AUTO, ROOT('courses')),
@ClassesXML = (SELECT * FROM @Classes class FOR XML AUTO, ROOT('classes')),
@InstructorsXML = (SELECT * FROM @Instructors instructor FOR XML AUTO, ROOT('instructors')),
@StudentsXML = (SELECT * FROM @Students student FOR XML AUTO, ROOT('students')),
@CompaniesXML = (SELECT * FROM @Companies FOR XML AUTO, ROOT('companies')),
@ClassInstructorsXML = (SELECT * FROM @ClassInstructors classinstructor FOR XML AUTO, ROOT('classinstructors'))
SELECT (SELECT @CoursesXML TYPE),
(SELECT @ClassesXML TYPE),
(SELECT @InstructorsXML TYPE),
(SELECT @StudentsXML TYPE),
(SELECT @CompaniesXML TYPE),
(SELECT @ClassInstructorsXML TYPE)
FOR XML RAW(''), ELEMENTS, ROOT('import')
and here is the xml it spits out:
<import>
<courses>
<course xid="DummyData-yDa" name="DummyData" />
<course xid="USA" name="DummyData" />
</courses>
<classes>
<class xid="DummyData" sd="DummyData" ed="DummyData" xloc="DummyData" xcourse="DummyData" lm="1" headcount="0" xvendor="DummyData" survey="no email" classname="DummyData, DummyData" />
<class xid="DummyData" sd="DummyData" ed="DummyData" xloc="DummyData" xcourse="DummyData" lm="1" headcount="0" xvendor="DummyData" survey="true" classname="DummyData, DummyData" />
<class xid="DummyData" sd="DummyData" ed="DummyData" xloc="DummyData" xcourse="DummyData" lm="1" headcount="8" xvendor="DummyData" survey="no email" classname="DummyData, DummyData" />
<class xid="DummyData" sd="DummyData" ed="DummyData" xloc="DummyData" xcourse="DummyData" lm="1" headcount="12" xvendor="DummyData" survey="no email" classname="DummyData, DummyData" />
<class xid="DummyData" sd="DummyData" ed="DummyData" xloc="DummyData" xcourse="DummyData" lm="1" headcount="18" xvendor="DummyData" survey="no email" classname="DummyData, DummyData" />
</classes>
<instructors>
<instructor xid="DummyData" fname="DummyData" lname="DummyData" email="DummyData" />
</instructors>
<students>
<student xid="DummyData" email="DummyData" firstname="DummyData" lastname="DummyData" title="DummyData" cancel="FALSE" />
<student xid="DummyData" email="DummyData" firstname="DummyData" lastname="DummyData" title="DummyData" cancel="TRUE" />
</students>
<companies>
<company xid="DummyData-DummyData" name="DummyData" />
</companies>
<classinstructors>
<classinstructor xid="DummyData" />
</classinstructors>
</import>
July 2, 2010 at 9:14 am
Using my example from before:
DECLARE @Vendors TABLE (xid varchar(2), name varchar(20), internal bit)
INSERT INTO @Vendors
SELECT 'V1', 'Corporate University', 1 UNION ALL
SELECT 'V2', 'Learning Provider', 0
DECLARE @Companies TABLE (xid varchar(3), name varchar(50))
INSERT INTO @Companies
SELECT 'CO1', 'ABC Corporation'
DECLARE @Courses TABLE (xid varchar(2), name varchar(50), [desc] varchar(150), ol bit)
INSERT INTO @Courses
SELECT 'C3', 'Basic Training Course', 'A course on basic training.', 0 UNION ALL
SELECT 'C4', 'Basic Training Course Web Based', 'An online course on basic training.', 1
DECLARE @VendorsXML xml,
@CompaniesXML xml,
@CoursesXML xml
SELECT @VendorsXML = (SELECT * FROM @vendors vendor FOR XML AUTO, ROOT('vendors')),
@CompaniesXML = (SELECT * FROM @companies comany FOR XML AUTO, ROOT('companies')),
@CoursesXML = (SELECT * FROM @Courses course FOR XML AUTO, ROOT('courses'))
SELECT GETDATE() '@date',
1234 '@src',
(SELECT @VendorsXML TYPE),
(SELECT @CompaniesXML TYPE),
(SELECT @CoursesXML TYPE)
FOR XML PATH('import')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply