SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need To Generate XML File


Need To Generate XML File

Author
Message
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
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
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2946 Visits: 8370
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



Clear Sky SQL
My Blog
vick.ram79
vick.ram79
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 428
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

How To Post
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
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18778 Visits: 20457
Saravanan, can you answer Dave Ballantyne's question? It is a critical point.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
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
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18778 Visits: 20457
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87374 Visits: 41113
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87374 Visits: 41113
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2946 Visits: 8370
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



Clear Sky SQL
My Blog
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