SQL Server table is converted into the xml file using query

Question

Hi,

i want how to sql server table record is converted into the xml file using sql script or ssis package.

Here i have mentioned the tables below.

Table name is organization

want this table record is converted into the xml file.

i want this same type of xml file. Here maintained the hierarchy level, hierarchy is very important.

<?xml version="1.0" encoding="UTF-8"?>
-<Root>
-<Node
Address="London, 120 Hanover Sq." Branch="TopManagement" Position="CEO" Phone="(205) 555 - 9898" Email="[e-mail]" LastName="Fuller" FirstName="Andrew">

-<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="President QA" Phone="(205) 555 - 9888" Email="[e-mail]" LastName="Boather" FirstName="Jeremy">

-<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="VP QA" Phone="(205) 555 - 9887" Email="[e-mail]" LastName="Dodsworth" FirstName="Anne">

-<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team1" Phone="(205) 555 - 9886" Email="[e-mail]" LastName="Tuckings" FirstName="Alexander">

<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="Senior QA" Phone="(205) 555 - 9885" Email="[e-mail]" LastName="Smith" FirstName="Brenda"/>
</Node>

<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team2" Phone="(205) 555 - 9885" Email="[e-mail]" LastName="Bird" FirstName="Mary"/>
</Node>
</Node>

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="President Dev Dept." Phone="(205) 555 - 9897" Email="[e-mail]" LastName="Buchanan" FirstName="Steven">

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="VP Dev Dept." Phone="(205) 555 - 9896" Email="[e-mail]" LastName="King" FirstName="Robert">

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team1" Phone="(205) 555 - 9892" Email="[e-mail]" LastName="Callahan" FirstName="Laura">

<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9872" Email="[e-mail]" LastName="Roland" FirstName="Jason">
</Node>
</Node>

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team2" Phone="(205) 555 - 9882" Email="[e-mail]" LastName="Danstin" FirstName="Eric">

<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9862" Email="[e-mail]" LastName="Lincoln" FirstName="Elizabeth">
</Node>

<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9852" Email="[e-mail]" LastName="Peacock" FirstName="Margaret">
</Node>
</Node>
</Node>
</Node>
</Node>
</Root>

i want above mentioned the xml file..

the table is converted into the above mentioned the xml file using sql query.

i try this query below mentioned query but this come same hierarchy structure but close node not come that means </node> this tag comes some lines u check above mentioned xml file.

SELECT s.Address AS [@Address],s.Branch as [@Branch],s.Position as [@Position],s.Phone as [@Phone],
s.Email as [@Email],S.LastName as [@LastName],s.FirstName as [@FirstName]

FROM ( SELECT Address,Branch,Position,Phone,Email,FirstName,LastName  FROM Organization
       --GROUP BY Address
      ) AS s
FOR XML PATH('Node'),
        ROOT('Root')

this query result comes  this format --

<Root>
  <Node Address="London, 120 Hanover Sq." Branch="TopManagement" Position="CEO" Phone="(205) 555 - 9898" Email="[e-mail]" LastName="Fuller" FirstName="Andrew" />
  <Node Address="London, 120 Hanover Sq." Branch="QA" Position="President QA" Phone="(205) 555 - 9888" Email="[e-mail]" LastName="Boather" FirstName="Jeremy" />
  <Node Address="London, 120 Hanover Sq." Branch="QA" Position="VP QA" Phone="(205) 555 - 9887" Email="[e-mail]" LastName="Dodsworth" FirstName="Anne" />
  <Node Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team1" Phone="(205) 555 - 9886" Email="[e-mail]" LastName="Tuckings" FirstName="Alexander" />
  <Node Address="London, 120 Hanover Sq." Branch="QA" Position="Senior QA" Phone="(205) 555 - 9885" Email="[e-mail]" LastName="Smith" FirstName="Brenda" />
  <Node Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team2" Phone="(205) 555 - 9885" Email="[e-mail]" LastName="Bird" FirstName="Mary" />
  <Node Address="London, 120 Hanover Sq." Branch="Development" Position="President Dev Dept." Phone="(205) 555 - 9897" Email="[e-mail]" LastName="Buchanan" FirstName="Steven" />
  <Node Address="London, 120 Hanover Sq." Branch="Development" Position="VP Dev Dept." Phone="(205) 555 - 9896" Email="[e-mail]" LastName="King" FirstName="Robert" />
  <Node Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team1" Phone="(205) 555 - 9892" Email="[e-mail]" LastName="Callahan" FirstName="Laura" />
  <Node Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9872" Email="[e-mail]" LastName="Roland" FirstName="Jason" />
  <Node Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team2" Phone="(205) 555 - 9882" Email="[e-mail]" LastName="Danstin" FirstName="Eric" />
  <Node Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9862" Email="[e-mail]" LastName="Lincoln" FirstName="Elizabeth" />
  <Node Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9852" Email="[e-mail]" LastName="Peacock" FirstName="Margaret" />
</Root>

i want this structure and some </node> close node tag also i will come. i want  mentioned above first xml file </node> this is mentioned in bold letter. this is i want

Regards,
Abdul Khadir.

Answer 1

Hi ,

            is this what u need?    

DECLARE @Organization TABLE (FirstName VARCHAR(20),
LastName VARCHAR(20),
Email VARCHAR(20),
Phone BIGINT,
Position VARCHAR(10),
Branch VARCHAR(10),
[Address] VARCHAR(100))
INSERT @Organization SELECT 'sunny','sara','[e-mail]','99990000','CEO','Cananda','Sunny st'
INSERT @Organization SELECT 'hansi','sat','[e-mail]','99991234','PM','India','sat st'   
SELECT [Address],Branch,Position,Phone,Email,LastName,FirstName
FROM @Organization AS [Organization] FOR XML AUTO ,ELEMENTS

---

Thanks & Regards, sathya

Answer 2

Hi ,

            is this what u need?    

DECLARE @Organization TABLE (FirstName VARCHAR(20),
LastName VARCHAR(20),
Email VARCHAR(20),
Phone BIGINT,
Position VARCHAR(10),
Branch VARCHAR(10),
[Address] VARCHAR(100))
INSERT @Organization SELECT 'sunny','sara','[e-mail]','99990000','CEO','Cananda','Sunny st'
INSERT @Organization SELECT 'hansi','sat','[e-mail]','99991234','PM','India','sat st'   
SELECT [Address],Branch,Position,Phone,Email,LastName,FirstName
FROM @Organization AS [Organization] FOR XML AUTO ,ELEMENTS

---

Thanks & Regards, sathya

Hi,

i want this xml xml file. same file i need,

<?xml version="1.0" encoding="UTF-8"?>
-<Root>
-<Node
Address="London, 120 Hanover Sq." Branch="TopManagement" Position="CEO" Phone="(205) 555 - 9898" Email="[e-mail]" LastName="Fuller" FirstName="Andrew">

-<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="President QA" Phone="(205) 555 - 9888" Email="[e-mail]" LastName="Boather" FirstName="Jeremy">

-<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="VP QA" Phone="(205) 555 - 9887" Email="[e-mail]" LastName="Dodsworth" FirstName="Anne">

-<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team1" Phone="(205) 555 - 9886" Email="[e-mail]" LastName="Tuckings" FirstName="Alexander">

<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="Senior QA" Phone="(205) 555 - 9885" Email="[e-mail]" LastName="Smith" FirstName="Brenda"/>
</Node>

<Node
Address="London, 120 Hanover Sq." Branch="QA" Position="Team Lead Team2" Phone="(205) 555 - 9885" Email="[e-mail]" LastName="Bird" FirstName="Mary"/>
</Node>
</Node>

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="President Dev Dept." Phone="(205) 555 - 9897" Email="[e-mail]" LastName="Buchanan" FirstName="Steven">

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="VP Dev Dept." Phone="(205) 555 - 9896" Email="[e-mail]" LastName="King" FirstName="Robert">

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team1" Phone="(205) 555 - 9892" Email="[e-mail]" LastName="Callahan" FirstName="Laura">

<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9872" Email="[e-mail]" LastName="Roland" FirstName="Jason">
</Node>
</Node>

-<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Team Lead Team2" Phone="(205) 555 - 9882" Email="[e-mail]" LastName="Danstin" FirstName="Eric">

<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9862" Email="[e-mail]" LastName="Lincoln" FirstName="Elizabeth">
</Node>

<Node
Address="London, 120 Hanover Sq." Branch="Development" Position="Senior Dev" Phone="(205) 555 - 9852" Email="[e-mail]" LastName="Peacock" FirstName="Margaret">
</Node>
</Node>
</Node>
</Node>
</Node>
</Root>

Regards,

Abdul Khadir.

Answer 3

Hi

SQL script

http://ask.sqlservercentral.com/questions/7158/output-sql-data-as-xml-and-export-to-xml-file.html

using SSIS. 

http://blog.jontav.com/post/7612017013/import-xml-into-sql-server-using-ssis

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 4

I thought I answered this query pretty thoroughly here:

http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/4f793bf6-1096-4716-8fb9-0e1e56936cab

Have you worked through that?