February 22, 2015 at 8:16 pm
I have below two table and i want ot generate the xml out put as mentioned below.
create table Candidates(eno int,ResumeText varchar(30), Email varchar(30),Active varchar(30),postalcode varchar(30),country int)
insert into Candidates
select 1,'Test','ee@ee.com','Active yes','888888',2
union all
select 2,'Test','ee@ee.com','Active yes','888888',6
create table CareerBuilderdatafeed (
ClientKey INT ,
VendorKey INT
)
insert into CareerBuilderdatafeed
select 88, 99
Output should be
<CareerBuilderdatafeed ClientKey="88" VendorKey="99">
<Candidates>
<Candidate>
<ResumeText>Test</ResumeText>
<Email>ee@ee.com</Email>
<Active>Activees</Active>
<Postal>888888</Postal>
<country>2</country>
</Candidate>
- <Candidate>
<ResumeText>Test</ResumeText>
<Email>ee@ee.com</Email>
<Active>Active yes</Active>
<Postal>888888</Postal>
<country>6</country>
</Candidate>
</Candidates>
</CareerBuilderdatafeed>
February 22, 2015 at 11:17 pm
Quick solution using FOR XML PATH and a nested query
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Candidates') IS NOT NULL DROP TABLE dbo.Candidates;
create table dbo.Candidates(eno int,ResumeText varchar(30), Email varchar(30),Active varchar(30),postalcode varchar(30),country int)
;
insert into dbo.Candidates
select 1,'Test','ee@ee.com','Active yes','888888',2
union all
select 2,'Test','ee@ee.com','Active yes','888888',6
;
IF OBJECT_ID(N'dbo.CareerBuilderdatafeed') IS NOT NULL DROP TABLE dbo.CareerBuilderdatafeed;
create table dbo.CareerBuilderdatafeed (
ClientKey INT ,
VendorKey INT
);
insert into dbo.CareerBuilderdatafeed
select 88, 99;
SELECT
CF.ClientKey AS '@ClientKey'
,CF.VendorKey AS '@VendorKey'
,(SELECT
CD.ResumeText
,CD.Email
,CD.Active
,CD.postalcode
,CD.country
FROM dbo.Candidates CD
FOR XML PATH('Candidate'), TYPE, ROOT(N'Candidates'))
FROM dbo.CareerBuilderdatafeed CF
FOR XML PATH('CareerBuilderdatafeed')
;
Results
<CareerBuilderdatafeed ClientKey="88" VendorKey="99">
<Candidates>
<Candidate>
<ResumeText>Test</ResumeText>
<Email>ee@ee.com</Email>
<Active>Active yes</Active>
<postalcode>888888</postalcode>
<country>2</country>
</Candidate>
<Candidate>
<ResumeText>Test</ResumeText>
<Email>ee@ee.com</Email>
<Active>Active yes</Active>
<postalcode>888888</postalcode>
<country>6</country>
</Candidate>
</Candidates>
</CareerBuilderdatafeed>
February 22, 2015 at 11:45 pm
Something like below.
SELECT ClientKey,VendorKey,
(SELECT eno,ResumeText,Email,Active,postalcode,country
FROM Candidates
FOR XML Path('Candidate'), TYPE, ROOT('Candidates'))
FROM CareerBuilderdatafeed
FOR XML RAW('CareerBuilderdatafeed')
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply