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 Friday, December 28, 2012 11:15 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
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
Post #1401136
Posted Saturday, December 29, 2012 2:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Kent user group
Post #1401152
Posted Saturday, December 29, 2012 7:26 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:49 PM
Points: 75, Visits: 409
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
Post #1401164
Posted Sunday, December 30, 2012 10:54 AM
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
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
Post #1401263
Posted Monday, December 31, 2012 1:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 5,158, Visits: 12,001
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1401306
Posted Monday, December 31, 2012 3:18 AM
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
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
Post #1401339
Posted Monday, December 31, 2012 3:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 5,158, Visits: 12,001
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1401342
Posted Monday, December 31, 2012 7:24 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:12 PM
Points: 35,347, Visits: 31,883
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."

(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 #1401386
Posted Monday, December 31, 2012 7:31 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:12 PM
Points: 35,347, Visits: 31,883
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."

(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 #1401390
Posted Monday, December 31, 2012 7:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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 :)




Clear Sky SQL
My Blog
Kent user group
Post #1401391
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse