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.
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.