Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need To Generate XML File


Need To Generate XML File

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45279 Visits: 39934
Dave Ballantyne (12/31/2012)
Jeff Moden (12/31/2012)


The fly in the ointment, of course, will be the non-unique leaf levels.


Indeed there are


->-> VP Dev Dept
->->-> Team Lead Team1
->->->-> Senior Dev
->->-> Team Lead Team2
->->->-> Senior Dev


So the rhetorical question is, how do we tell that "Senior Dev" reports to "Team Lead Team 1" or "Team Lead Team 2" or 3,4,5 etc ?

Answer is we cant.

A parent/child hierarchy is the simplest way to introduce this relationship , and will be extensible once teams 4,5,6 are created and when cleaners and tea makers are also introduced into the mix Smile


Absolutely correct on all points.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 1721
sarwaanmca (12/28/2012)
I am already tried following Method..

select * from xmldata FOR XML AUTO

select * from xmldata FOR XML AUTO, ELEMENTS

SELECT * FROM xmldata FOR XML PATH('Root')

select * from xmldata FOR XML RAW('Node')

But still i Didn't get Exact File Structure...
(Above Mentioned File Structure The Parent / Child hierarchy Should be Must)
Any One Try this...ASAP...

Thanks & Regards,
Saravanan.D


Some good questions have been raised concerning the data structure, so I won't address that. Assuming you eventually get the data output the way you need it you will still face the XML formatting nightmare. I'll offer some links that give specific examples for producing various forms of XML output:

http://www.sqlservercentral.com/Forums/Topic1361033-392-3.aspx#bm1375776

http://www.sqlservercentral.com/Forums/Topic1344161-391-1.aspx#bm1345086

http://www.sqlservercentral.com/Forums/Topic1395950-1292-1.aspx#bm1397564

I hope these examples will help you.

 
Tavis Reddick
Tavis Reddick
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 191
As other posters have pointed out, you will need the child-parent relationship from your relational database tables to construct the XML hierarchy. Try this:

-- Create table, but this time add an identifier and a ReportsTo column referencing this.
CREATE TABLE [dbo].[xmldata](
[Identifier] SMALLINT, -- unique identifier
[FirstName] [varchar](110) NULL,
[LastName] [varchar](210) NULL,
[Email] [varchar](110) NULL,
[Phone] [varchar](110) NULL,
[Position] [varchar](110) NULL,
[Branch] [varchar](110) NULL,
[Address] [varchar](110) NULL,
[ReportsTo] SMALLINT NULL -- person reports to someone in this table (or nobody)
CONSTRAINT [PK_xmldata] PRIMARY KEY CLUSTERED
(
[Identifier] ASC
)) ON [PRIMARY]
GO
INSERT [dbo].[xmldata] ([Identifier], [FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address], [ReportsTo])
VALUES (1, N'Andrew', N'Fuller', N'afuller@contoso.com', N'(205) 555 - 9898', N'CEO', N'TopManagement', N'London, 120 Hanover Sq.', NULL)
, (2, N'Jeremy', N'Boather', N'jboather@contoso.com', N'(205) 555 - 9888', N'President QA', N'QA', N'London, 120 Hanover Sq.', 1)
, (3, N'Anne', N'Dodsworth', N'adodsworth@contoso.com', N'(205) 555 - 9887', N'VP QA', N'QA', N'London, 120 Hanover Sq.', 2)
, (4, N'Alexander', N'Tuckings', N'atuckings@contoso.com', N'(205) 555 - 9886', N'Team Lead Team1', N'QA', N'London, 120 Hanover Sq.', 3)
, (5, N'Brenda', N'Smith', N'bsmith@contoso.com', N'(205) 555 - 9885', N'Senior QA', N'QA', N'London, 120 Hanover Sq.', 4)
, (6, N'Mary', N'Bird', N'mbird@contoso.com', N'(205) 555 - 9885', N'Team Lead Team2', N'QA', N'London, 120 Hanover Sq.', 3)
, (7, N'Steven', N'Buchanan', N'sbuchanan@contoso.com', N'(205) 555 - 9897', N'President Dev Dept.', N'Development', N'London, 120 Hanover Sq.', 1)
, (8, N'Robert', N'King', N'rking@contoso.com', N'(205) 555 - 9896', N'VP Dev Dept.', N'Development', N'London, 120 Hanover Sq.', 7)
, (9, N'Laura', N'Callahan', N'lcallahan@contoso.com', N'(205) 555 - 9892', N'Team Lead Team1', N'Development', N'London, 120 Hanover Sq.', 8)
, (10, N'Jason', N'Roland', N'jroland@contoso.com', N'(205) 555 - 9872', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.', 9)
, (11, N'Eric', N'Danstin', N'edanstin@contoso.com', N'(205) 555 - 9882', N'Team Lead Team2', N'Development', N'London, 120 Hanover Sq.', 8)
, (12, N'Elizabeth', N'Lincoln', N'elincoln@contoso.com', N'(205) 555 - 9862', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.', 11)
, (13, N'Margaret', N'Peacock', N'mpeacock@contoso.com', N'(205) 555 - 9852', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.', 11);
-- Check data
SELECT *
FROM [dbo].[xmldata];
GO
-- Function to create child nodes; uses recursion and a stopper.
--CREATE FUNCTION dbo.NodeBuilder
CREATE FUNCTION dbo.NodeBuilder
(@ReportsTo SMALLINT)
RETURNS XML
AS
BEGIN
DECLARE @Node XML
SET @Node =
(SELECT FirstName AS '@FirstName',
LastName AS '@LastName',
Email AS '@Email',
Phone AS '@Phone',
Position AS '@Position',
Branch AS '@Branch',
[Address] AS '@Address',
CASE
WHEN (SELECT COUNT(*) FROM dbo.xmldata AS P2 WHERE P2.Identifier = P1.ReportsTo) > 0
THEN (SELECT dbo.NodeBuilder(Identifier))
ELSE NULL
END
FROM dbo.xmldata AS P1
WHERE ReportsTo = @ReportsTo
FOR XML PATH('Node'), TYPE)
RETURN @Node
END
;
GO
-- XML builder, uses function to create child nodes.
SELECT
P0.FirstName AS '@FirstName',
P0.LastName AS '@LastName',
P0.Email AS '@Email',
P0.Phone AS '@Phone',
P0.Position AS '@Position',
P0.Branch AS '@Branch',
P0.[Address] AS '@Address',
(SELECT dbo.NodeBuilder(Identifier))
FROM dbo.xmldata AS P0
WHERE P0.ReportsTo IS NULL
FOR XML PATH('Node'), TYPE, ROOT('Root')


I would probably tidy up the last SELECT statement and put it in a stored procedure with Identifier as the parameter, so subsections of your organizational tree can be more easily selected.
sarwaanmca
sarwaanmca
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 193
wow fantastic work Mr.Tavis Reddick...
thank for ur support...
I Need to Speak About this Result to My Team Lead..
Here After We Decide to if it is a Source XML File is Very Huge Means How to Include The Position Id (ReportsTo)...
thanks to all...



Regards,
Saravanan.D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search