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

BCP Format File

I am new to BCP. Can anyone help me understand this? I tried searching
the BOL but it doesnt show much help on syntex. Lets say I have a
simple table and want to out put the records into a file. Then with
that output file, I want to create a format file using the BCP utillity
and builk insert it into another table. Can someone explain me the
syntex involved in this.. No need to go so elabrate.. Just give me a
basic senario and just the syntex...

Thanks...Hi

You will only need to use a format file if you want to manipulate the data
in your output file in some way.
If your tables have the same format then to output the data in to a file:
bcp "Northwind..Customers" out "Customers.txt" -n -S Server1 -U"Jane
Doe" -P"go dba"

To populate the table (with the same name) into Tempdb on Server2 using a
trusted connection
bcp "Tempdb..Customers" in "Customers.txt" -n -S Server2 -T

If you only want to extract a subset or possibly change the order, you could
either limit the output using the queryout option
bcp "SELECT CustomerID, CompanyName, ContactName FROM Northwind..Customers"
queryout "Customers.txt" -n -S Server1 -U"Jane Doe" -P"go dba"

If you want to use a format file it is sometime useful to one using the
format option and then work with that. Look at the section "Using Format
Files" and that is clear on how to create/change them.

For example:
bcp "Northwind..Customers" format Customers.txt -f Customers.bcp -n -T -S
(local)
Produces the format file Customers.bcp:
8.0
11
1 SQLNCHAR 2 10 "" 1
CustomerID Latin1_General_CI_AS
2 SQLNCHAR 2 80 "" 2
CompanyName Latin1_General_CI_AS
3 SQLNCHAR 2 60 "" 3
ContactName Latin1_General_CI_AS
4 SQLNCHAR 2 60 "" 4
ContactTitle Latin1_General_CI_AS
5 SQLNCHAR 2 120 "" 5
Address Latin1_General_CI_AS
6 SQLNCHAR 2 30 "" 6 City
Latin1_General_CI_AS
7 SQLNCHAR 2 30 "" 7 Region
Latin1_General_CI_AS
8 SQLNCHAR 2 20 "" 8
PostalCode Latin1_General_CI_AS
9 SQLNCHAR 2 30 "" 9
Country Latin1_General_CI_AS
10 SQLNCHAR 2 48 "" 10 Phone
Latin1_General_CI_AS
11 SQLNCHAR 2 48 "" 11 Fax
Latin1_General_CI_AS

To create a customers.txt file with data in it using this format file:
bcp "Northwind..Customers" out Customers.txt -f Customers.bcp -T -S(local)

If I create a Customers table in tempdb then I can input the data using the
command

bcp "Tempdb..Customers" in Customers.txt -f Customers.bcp -T -S(local)

HTH

John

"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1109369544.975842.308250@.f14g2000cwb.googlegr oups.com...
>I am new to BCP. Can anyone help me understand this? I tried searching
> the BOL but it doesnt show much help on syntex. Lets say I have a
> simple table and want to out put the records into a file. Then with
> that output file, I want to create a format file using the BCP utillity
> and builk insert it into another table. Can someone explain me the
> syntex involved in this.. No need to go so elabrate.. Just give me a
> basic senario and just the syntex...
> Thanks...