SQL Server Table data is moved into the XML File using T-SQL Query

Question

Hi,

 i have one sql server table. that table data is moved or convert to the xml file.. this xml file is open on separately on internet explorer.

Table Name - organization

this is table structure.

CREATE TABLE [dbo].[Organization](
       [FirstName] [varchar](max) NULL,
       [LastName] [varchar](max) NULL,
       [Branch] [varchar](max) NULL,
       [Position] [varchar](max) NULL,
       [Phone] [varchar](max) NULL,
       [Email] [varchar](max) NULL,
       [Address] [varchar](max) NULL
) ON [PRIMARY]

i try this below mentioned query, this is working fine but this is result come like this(see below).

i want separate xml file in result. that is opened in internet explorer.

--this is query---
SELECT
   FirstName as "@FirstName",
   LastName as "LastName",
   Branch as "Branch",
   Position as "Position",
   Phone as "Phone",
   Email as "Email",
   Address as "Address"
FROM Organization
FOR XML PATH('Organization')

--this is result--- i don't need this type of results...

but i want separate xml file in this result file...

Regards,

Abdul Khadir.

Answer 1

Please explain clearly your problem.!

It will give you xml but to open it in internet explorer, you have to append the xml opening tag at the start of the xml.

eg:

<?xml version="1.0"?>

---

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

Answer 2

Please explain clearly your problem.!

It will give you xml but to open it in internet explorer, you have to append the xml opening tag at the start of the xml.

eg:

<?xml version="1.0"?>

---

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

Hi,

this is my table structure.

CREATE TABLE [dbo].[Organization](
       [FirstName] [varchar](max) NULL,
       [LastName] [varchar](max) NULL,
       [Branch] [varchar](max) NULL,
       [Position] [varchar](max) NULL,
       [Phone] [varchar](max) NULL,
       [Email] [varchar](max) NULL,
       [Address] [varchar](max) NULL
       
) ON [PRIMARY]

this is my table data.

i want this table data is moved to the xml file using t-sql scripts..

Regards,

Abdul Khadir.

Answer 3

Try this :

DECLARE @xml_string VARCHAR(8000)
DECLARE @FirstName VARCHAR(100)
DECLARE @LastName VARCHAR(100)
DECLARE @Branch CHAR(256)
DECLARE @Position VARCHAR(100)
DECLARE @Phone VARCHAR(100)
DECLARE @Email VARCHAR(100)
DECLARE @Address VARCHAR(100)

DECLARE cur_val CURSOR FOR SELECT FirstName, LastName, Branch,
          Position, Phone, Email,
          Address FROM PDBUser

SET @FirstName = '';
SET @LastName = '';
SET @Branch = '';
SET @Position = '';
SET @Phone = '';
SET @Email = '';
SET @Address = '';
SET @xml_string = '<Organization>';

OPEN cur_val
BEGIN
  FETCH NEXT FROM cur_val INTO @FirstName, @LastName, @Branch, @Position, @Phone ,@Email , @Address
  SET @xml_string = @xml_string +
       '<Record>' +
        '<FirstName>' + ltrim(rtrim(@FirstName)) + '</FirstName>' +
        '<LastName>' + ltrim(rtrim(@FirstName)) + '</LastName>' +
        '<Branch>' + ltrim(rtrim(@FirstName)) + '</Branch>' +
        '<Position>' + ltrim(rtrim(@FirstName)) + '</Position>' +
        '<Phone>' + ltrim(rtrim(@FirstName)) + '</Phone>' +
        '<Email>' + ltrim(rtrim(@FirstName)) + '</Email>' + 
        '<Address>' + ltrim(rtrim(@FirstName)) + '</Address>' + 
       '</Record>'
 WHILE @@FETCH_STATUS = 0
 BEGIN
  FFETCH NEXT FROM cur_val INTO @FirstName, @LastName, @Branch, @Position, @Phone ,@Email , @Address
  SET @xml_string = @xml_string +
       '<Record>' +
        '<FirstName>' + ltrim(rtrim(@FirstName)) + '</FirstName>' +
        '<LastName>' + ltrim(rtrim(@FirstName)) + '</LastName>' +
        '<Branch>' + ltrim(rtrim(@FirstName)) + '</Branch>' +
        '<Position>' + ltrim(rtrim(@FirstName)) + '</Position>' +
        '<Phone>' + ltrim(rtrim(@FirstName)) + '</Phone>' +
        '<Email>' + ltrim(rtrim(@FirstName)) + '</Email>' + 
        '<Address>' + ltrim(rtrim(@FirstName)) + '</Address>' + 
       '</Record>'
 END
END

CLOSE cur_val
DEALLOCATE cur_val
SET @xml_string = @xml_string + '</Organizationr>'
SELECT @xml_string

---

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

Answer 4

Since the E-mail addresses from contoso.com, I conclude that you are learning.

So here is the lesson: you use SQL Server to produce the data. And, yes, you can produce the XML in SQL Server. But you do (normally) not use SQL Server to create files. Instead you have a client program that receives the data from SQL Server and creates the file. And since you can create XML just as well in the client, you can retreive the data from SQL Server in relational format.

If you want to create the file without writing your own client, I would expect this to be possible with SSIS, SQL Server Integration Services. Alas, I'm ignorant of SSIS myself, and I can't seem to find any XML option in the Import/Export wizard.

I would also like to point out that the script that Satya posted is not going to work if the data includes characters that are special to XML (and there are a few that are.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Yes Erland, you are right. My script can give an xml structure to the date but not a proper validated xml.
If i had to implement it i would have done in my program, not on sql side.

---

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

Answer 6

Yes Erland, you are right. My script can give an xml structure to the date but not a proper validated xml.
If i had to implement it i would have done in my program, not on sql side.

---

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

Hi,

thank you for response.

i want the solution. i want separate xml is stored in some folder. 

the sql server table is moved to one xml file. that xml is stored in one  folder.

this is need for me. i am waiting for reply.

Regards,

Abdul Khadir.

Answer 7

Hi Abdul,

You should make a Windows service for this, that will pull the data and create the xml file in that folder.

---

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

Answer 8

Hi Abdul,

You should make a Windows service for this, that will pull the data and create the xml file in that folder.

---

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

Hi,

i didnt understand the your concepts. please explain very clear to me.

Regards,

Abdul Khadir.

Answer 9

i didnt understand the your concepts. please explain very clear to me.

So did you read my post?

The gist is simple: you should not do this in T-SQL. This is not what you use T-SQL for. You should write a client program for the task. (But you don't have to make it a service as Satya suggested.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

i didnt understand the your concepts. please explain very clear to me.

So did you read my post?

The gist is simple: you should not do this in T-SQL. This is not what you use T-SQL for. You should write a client program for the task. (But you don't have to make it a service as Satya suggested.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Hi,

i had tried this concept using ssis, that is working fine. separate xml file is opened but hierarchy is not come.

i want same hierarchy.

Regards,

Abdul Khadir.