BCP Queryout export to XML Question

Hey All,
Thought I'd take a break from Scalar Functions for a while and give Omnibuzz
a break from answering them.
I've searched thru the BOL, and this forum, as well as the help files in
SQL2005. I've gotten to the point where I am exporting the data
correctly..however I am looking for a format..
Here's what I'm attempting.. I need to take certain grabs of data using a
stored procedure.. dump it to an xml file / soap file, and then publish to
another service. for that to happen, i have a specific file format that the
y
have to be in.
So, here's my data...
strike price nominalDate
1000 0.000 2006-04-01
10000 2.850 2006-04-01
10050 0.000 2006-04-01
10150 0.000 2006-04-01
10200 0.000 2006-04-01
Here's the optionformat.xml
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="10"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="nominalDate" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="price" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="strike" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
and here's the real format it needs to be in.
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Of course, this doesn't include the headers at all, but I'm thinking I can
just add that to the file using the copy command to join the 3 files (copy
txt1.txt + optionformat.xml + txt2.txt uploadfile.xml)
The hardest part is getting it in that format...
Now to have the answer plunked right in front of me would be nice, but I
really need to learn how to do this, and understand the process... The only
other option that i have is to write a small vb program that calls from the
database, formats the data using the FileSystemObject.
If you know of a good resource that explains exporting into formatted xml,
that would be great.
If I'm totally going down the wrong road on this solution, let me know as
well.
Thanks!
~Dan Regalia
--
www.krushradio.com - Internet Radio for the rest of usDaniel Regalia (DanielRegalia@.discussions.microsoft.com) writes:
> Here's what I'm attempting.. I need to take certain grabs of data using
> a stored procedure.. dump it to an xml file / soap file, and then
> publish to another service. for that to happen, i have a specific file
> format that they have to be in.
>...
> Here's the optionformat.xml
><?xml version="1.0"?>
><BCPFORMAT
> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
>...
> and here's the real format it needs to be in.
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Ehum, you cannot use a BCP format file to specify an XML format for
the output.
You should probably look into using FOR XML EXPLICIT instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx