How to export SQL Server 2000 data into XML file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to export SQL Server 2000 data into XML file

Author

Topic

kwilliams

194 Posts

Posted - 2005-11-21 : 15:01:36

I'm a complete newbie to this idea, but this is what I'd like to do.

I want to export data from SQL Server 2000 DB table into an already existing XML file. I'd like to do so using a Stored Procedure, which I would then add to a scheduled job.

I've been messing with the built-in Data Transformation jobs within SQL Server 2000 with some success. I'm able to export table data into an XML file like this:

CREATE TABLE B:\sample.xml (
id integer (12) NOT NULL, 
fname varchar (10) NULL, 
lname varchar (10) NULL
)

The output looks like this:


1	Joe	Schmo
2	John	Doe

This is the output that I'd like to end up with:

<root>
	
	<sample>
		<id>1</id>
		<fname>Joe</fname>
		<lname>Schmo</lname>
	</sample>
	
	<sample>
		<id>2</id>
		<fname>John</fname>
		<lname>Doe</lname>
	</sample>
	
</root>

or this:

<root>
	
	<sample id="1">
		<fname>Joe</fname>
		<lname>Schmo</lname>
	</sample>

	<sample id="2">
		<fname>John</fname>
		<lname>Doe</lname>
	</sample>
	
</root>

Any information or resources would be greatly appreciated. Thanks.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-21 : 15:22:51

Check out Books On Line for more documentation on the SELECT clause ... particularly:
select .....
FOR XML AUTO, ELEMENTS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 15:22:53

look up
FOR XML
in BOL.
that should help.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 15:23:17

Go with the flow & have fun! Else fight the flow

kwilliams

194 Posts

Posted - 2005-11-21 : 15:31:57

Thanks for the quick replies.

I have looked FOR XML up a bit, but I'll make sure to read further. Since I want to save the XML data into an actual XML file, would I need to use OPENXML for that? Or is there a way to use the FOR XML clause and also save the results to a specified XML file?

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

kwilliams

194 Posts

Posted - 2005-11-21 : 15:49:11

Wow, I think that I've figured out a great way to accomplish what I need using the SQL XML templates. This is my plan:
1) Create Stored Procedures to SELECT, INSERT, or UPDATE database table data
2) Call that Stored Procedure from an XML file using SQL XML templates
3) Manipulate that XML file with a XSLT stylesheet
4) Transform the XML & XSLT docs server-side using the transformNode method

What do you think of this idea? Have you used this setup in the past? If so, has it worked well? Thanks again for your help.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-21 : 15:55:14

I like to create my own XML


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE [B:\sample.xml] (
id integer NOT NULL, 
fname varchar (10) NULL, 
lname varchar (10) NULL
)
GO

INSERT INTO [B:\sample.xml] ([id], fname, lname)
SELECT 1,	'Joe',	'Schmo' UNION ALL
SELECT 2,	'John',	'Doe'
GO

SELECT XML_Out FROM (
SELECT '<root>' AS XML_Out
	, 0 AS [id]
	, 1 AS XML_Order
UNION ALL
SELECT REPLICATE(' ',15)+'<sample>' AS XML_Out
	, [id]
	, 2 AS XML_Order
  FROM [B:\sample.xml]
UNION ALL
SELECT REPLICATE(' ',30) + '<id>' + CONVERT(varchar(15),[id]) + '</id>' AS XML_Out
	, [id]
	, 3 AS XML_Order
  FROM [B:\sample.xml]
UNION ALL
SELECT REPLICATE(' ',30) + '<fname>' + fname + '</fname>' AS XML_Out
	, [id]
	, 4 AS XML_Order
  FROM [B:\sample.xml]
UNION ALL
SELECT REPLICATE(' ',30) + '<lname>' + lname + '</lname>' AS XML_Out
	, [id]
	, 5 AS XML_Order
  FROM [B:\sample.xml]
UNION ALL
SELECT REPLICATE(' ',15)+'<sample>' AS XML_Out
	, [id]
	, 6 AS XML_Order
  FROM [B:\sample.xml]
UNION ALL
SELECT '<root>' AS XML_Out
	, 9 AS [id]
	, 7 AS XML_Order
) AS XXX ORDER BY [id], XML_Order

GO

SET NOCOUNT ON
DROP TABLE [B:\sample.xml]
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

kwilliams

194 Posts

Posted - 2005-11-21 : 17:48:42

Thanks for the sample code. I tried your code on my DB table, but received the following error:
Microsoft SQL-DMO (ODBC SQL State 42S02)
Error 208: Invalid object name 'B:\sample.xml'.

I also tried putting the fullpath in instead of B:\sample.xml, but I received the same error message. Any suggestions?

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

kwilliams

194 Posts

Posted - 2005-11-22 : 17:23:55

Hello again,

Well, I've made a lot of progress from this morning. I created the following Stored Procedure:
CREATE PROCEDURE [dbo].[spsample] AS
SELECT * FROM sample FOR XML AUTO,ELEMENTS
GO

and then I executed the SP from Query Analyzer. The data filled the XML tags exactly how I'd like. Now I just need to save the SP's results to an XML file on the same server within a <root> tag. A great tutorial (http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#4d) gives some good info on using the queryout method along with 2 text files that contain the opening & closing <root> tags, but I'm not sure how I'm supposed to add this code to my SP. Have you worked with this method before? If so, could you let me know of the best way to add it to my existing SP, or point me in the right direction? Thanks for any help.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources