Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Need To Generate XML File Expand / Collapse
Author
Message
Posted Monday, December 31, 2012 7:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:46 PM
Points: 36,944, Visits: 31,446
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 :)


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401392
Posted Tuesday, January 1, 2013 1:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1401622
Posted Thursday, January 17, 2013 6:41 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 4:55 AM
Points: 24, Visits: 122
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.
Post #1408398
Posted Friday, January 18, 2013 11:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
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
Post #1409164
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse