December 28, 2012 at 11:15 pm
Hi,
I Have Table Data For The Following Stucture..
CREATE TABLE [dbo].[xmldata](
[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
) ON [PRIMARY]
GO
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Andrew', N'Fuller', N'afuller@contoso.com', N'(205) 555 - 9898', N'CEO', N'TopManagement', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Jeremy', N'Boather', N'jboather@contoso.com', N'(205) 555 - 9888', N'President QA', N'QA', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Anne', N'Dodsworth', N'adodsworth@contoso.com', N'(205) 555 - 9887', N'VP QA', N'QA', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Alexander', N'Tuckings', N'atuckings@contoso.com', N'(205) 555 - 9886', N'Team Lead Team1', N'QA', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Brenda', N'Smith', N'bsmith@contoso.com', N'(205) 555 - 9885', N'Senior QA', N'QA', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Mary', N'Bird', N'mbird@contoso.com', N'(205) 555 - 9885', N'Team Lead Team2', N'QA', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Steven', N'Buchanan', N'sbuchanan@contoso.com', N'(205) 555 - 9897', N'President Dev Dept.', N'Development', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Robert', N'King', N'rking@contoso.com', N'(205) 555 - 9896', N'VP Dev Dept.', N'Development', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Laura', N'Callahan', N'lcallahan@contoso.com', N'(205) 555 - 9892', N'Team Lead Team1', N'Development', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Jason', N'Roland', N'jroland@contoso.com', N'(205) 555 - 9872', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Eric', N'Danstin', N'edanstin@contoso.com', N'(205) 555 - 9882', N'Team Lead Team2', N'Development', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Elizabeth', N'Lincoln', N'elincoln@contoso.com', N'(205) 555 - 9862', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.')
INSERT [dbo].[xmldata] ([FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address]) VALUES (N'Margaret', N'Peacock', N'mpeacock@contoso.com', N'(205) 555 - 9852', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.')
Using This Table Data ... I want To Generate the xml File like ( Required File Structure )
<Root>
<Node FirstName="Andrew" LastName="Fuller" Email="afuller@contoso.com" Phone="(205) 555 - 9898" Position="CEO" Branch="TopManagement" Address="London, 120 Hanover Sq.">
<Node FirstName="Jeremy" LastName="Boather" Email="jboather@contoso.com" Phone="(205) 555 - 9888" Position="President QA" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Anne" LastName="Dodsworth" Email="adodsworth@contoso.com" Phone="(205) 555 - 9887" Position="VP QA" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Alexander" LastName="Tuckings" Email="atuckings@contoso.com" Phone="(205) 555 - 9886" Position="Team Lead Team1" Branch="QA" Address="London, 120 Hanover Sq.">
<Node FirstName="Brenda" LastName="Smith" Email="bsmith@contoso.com" Phone="(205) 555 - 9885" Position="Senior QA" Branch="QA" Address="London, 120 Hanover Sq."/>
</Node>
<Node FirstName="Mary" LastName="Bird" Email="mbird@contoso.com" Phone="(205) 555 - 9885" Position="Team Lead Team2" Branch="QA" Address="London, 120 Hanover Sq."/>
</Node>
</Node>
<Node FirstName="Steven" LastName="Buchanan" Email="sbuchanan@contoso.com" Phone="(205) 555 - 9897" Position="President Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Robert" LastName="King" Email="rking@contoso.com" Phone="(205) 555 - 9896" Position="VP Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Laura" LastName="Callahan" Email="lcallahan@contoso.com" Phone="(205) 555 - 9892" Position="Team Lead Team1" Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Jason" LastName="Roland" Email="jroland@contoso.com" Phone="(205) 555 - 9872" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
</Node>
<Node FirstName="Eric" LastName="Danstin" Email="edanstin@contoso.com" Phone="(205) 555 - 9882" Position="Team Lead Team2" Branch="Development" Address="London, 120 Hanover Sq.">
<Node FirstName="Elizabeth" LastName="Lincoln" Email="elincoln@contoso.com" Phone="(205) 555 - 9862" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
<Node FirstName="Margaret" LastName="Peacock" Email="mpeacock@contoso.com" Phone="(205) 555 - 9852" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
</Node>
</Node>
</Node>
</Node>
</Node>
</Root>
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
December 29, 2012 at 2:47 am
Hi,
you seem to be missing some very important data here.
your xml shows that "Brenda Smith" is a child of "Alexander Tuckings", but why ?
What data specifies that relationship ?
Dave
December 29, 2012 at 7:26 am
As Dave mentioned - there is no relationship rule given. So it is not possible to come up with the output that the OP is asking for.
But I suspect that the OP wants to use the [Position] column to tell the parent-child/manager-employee/Darth Vader-Luke Vader relationship.
So, the heirarchy goes:
[CEO] is root
-> [President *] next
-> -> [VP *] next
-> -> -> [Team Lead *] next
-> -> -> -> [Senior *] last
There are some obvious issues with this rule. [What happens if you are a Senior VP]?
This is what I could think of...
Sunil
December 30, 2012 at 10:54 am
Hi Sunil ,
The Required Heirarchy is,
[ CEO ]
1 -> President QA
->-> VP QA
->->-> Team Lead Team1
->->->-> Senior QA
->->-> Team Lead Team2
2 -> President Dev Dept
->-> VP Dev Dept
->->-> Team Lead Team1
->->->-> Senior Dev
->->-> Team Lead Team2
->->->-> Senior Dev
->->->-> Senior Dev
Pls Try this one...
Thanks & Regards,
Saravanan
December 31, 2012 at 1:35 am
Saravanan, can you answer Dave Ballantyne's question? It is a critical point.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 31, 2012 at 3:18 am
Hi Dave Ballantyne,
You Asked Me...
Hi,
you seem to be missing some very important data here.
your xml shows that "Brenda Smith" is a child of "Alexander Tuckings", but why ?
What data specifies that relationship ?
But Actual Relationship is Position.
Alexander Tuckings Position is Team Lead Team1
Brenda Smith Position is Senior QA
so that means Brenda Smith was subordinator of Alexander Tuckings
It Seems like Management Heirarchy ==> Top Level Management To Lower Level Management
you do one think please Refer my given Table that data Shows Actual Heirarchy Order so you Aditionaly Insert the Identity Column or Serial No ....
I Think I will be Very Usefull to u ... To Get Result...
Please Try it...
Thanks & Regards,
Saravanan.D
December 31, 2012 at 3:28 am
so that means Brenda Smith was subordinator of Alexander Tuckings
Why? Why not a subordinate of someone else?
There is no ordering of data and there is no way for SQL to know that one job title is 'lower' than another.
The usual way of accomplishing such a task is to include another couple of columns in your data:
1) PersonId - uniquely identifies a person
2) ManagerId - a foreign key back to PersonId
Without these, I can't see an easy way forward for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 31, 2012 at 7:24 am
sarwaanmca (12/28/2012)
Hi,I Have Table Data For The Following Stucture..
CREATE TABLE [dbo].[xmldata]([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
) ON [PRIMARY]
GO
Hmmm.... on my way to work so I can't do it right now but there might be a way to do this. We know that the Position column doesn't contain anything that supports the required relationship calculations. However, sarwaanmca has laid out what the relationships are in a post further above. Perhaps the answer is to create a table with the postion name (from the position column), an "ID", a "ParentID", and a "Level" column to impart the necessary relationship information to the data.
The fly in the ointment, of course, will be the non-unique leaf levels.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2012 at 7:31 am
sarwaanmca (12/30/2012)
Hi Sunil ,The Required Heirarchy is,
[ CEO ]
1 -> President QA
->-> VP QA
->->-> Team Lead Team1
->->->-> Senior QA
->->-> Team Lead Team2
2 -> President Dev Dept
->-> VP Dev Dept
->->-> Team Lead Team1
->->->-> Senior Dev
->->-> Team Lead Team2
->->->-> Senior Dev
->->->-> Senior Dev
Pls Try this one...
Thanks & Regards,
Saravanan
Even with the nicely laid out structure you have above, there is no way that the current data you've provided will support identifying whether someone with the position of "Senior Dev" should be a member of the "Team lead Team1" node or the "Team Lead Team2" node.
Your data needs a "PositionID" and a "ParentPositionID" column to do this task consistently. Please check your original table and see if such things are available. If they aren't, then your team will need to add them either directly to the table or as a sister table. If that can't be done, this project is doomed to failure.
An even more effective alternative to PositionID would be to have EmployeeID (or some such) and a ParentEmployeeID column.
In either case, the PositionID or the EmployeeID must be a unique number to support this task properly.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2012 at 7:32 am
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 🙂
December 31, 2012 at 7:35 am
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
Change is inevitable... Change for the better is not.
January 1, 2013 at 1:14 pm
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.
January 17, 2013 at 6:41 am
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.
January 18, 2013 at 11:20 pm
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply