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
2012年3月22日星期四
2012年2月23日星期四
BCP : Format File : Second Question
Before answering this question please read my previous post 10 minutes ago.
Assuming that I have to use a Format File with bcp to export a table that
has a column I don't want exported.
What Format File should I create in order to achive the following two goals:
I wish to create the smallest data file possible, and would be the fastest
for the server to process in both directions (out/in).
Can you provide the syntax to generate the format file?
Thanks
Russell Mangel
Las Vegas, NVRussell Mangel,
Check arguments -n and -N in BOL.
AMB
"Russell Mangel" wrote:
> Before answering this question please read my previous post 10 minutes ago
.
> Assuming that I have to use a Format File with bcp to export a table that
> has a column I don't want exported.
> What Format File should I create in order to achive the following two goal
s:
> I wish to create the smallest data file possible, and would be the fastest
> for the server to process in both directions (out/in).
> Can you provide the syntax to generate the format file?
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>|||Yeah, I made some tests.
The database I have, does not have any UniCode columns, and so when I export
using -n the data does get much smaller.
However, since I am transferring the data via FTP, I ran PKZIP on the data
files and discovered that it does a great job of compressing UniCode, so it
turns I will use -N switch anyway.
Thanks Again.
Russell Mangel
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BFF9DF60-13D3-48CC-A7FA-979B23DA0BD4@.microsoft.com...
> Russell Mangel,
> Check arguments -n and -N in BOL.
>
> AMB
>
> "Russell Mangel" wrote:
>|||Russell Mangel (russell@.tymer.net) writes:
> Before answering this question please read my previous post 10 minutes
> ago.
> Assuming that I have to use a Format File with bcp to export a table that
> has a column I don't want exported.
> What Format File should I create in order to achive the following two
> goals:
> I wish to create the smallest data file possible, and would be the fastest
> for the server to process in both directions (out/in).
> Can you provide the syntax to generate the format file?
No, not without the table definition.
The fast format is native format, which means that you need to describe
each column correctly. This is a little more tricky that describing a
character format. Then again, anything goes, so if you have a mix of
native and charcter format that is not much of a distaster.
You will probably find it easier to use a view together as I suggested in my
other post together with the -n or -N option as suggested by Alejandro.
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|||[posted and mailed]
Erland Sommarskog (esquel@.sommarskog.se) writes:
> Russell Mangel (russell@.tymer.net) writes:
> No, not without the table definition.
I spoke to soon! I forgot about the format option. You can do this:
bcp Northwind..Orders format dummy.txt -f Orders.fmt -N -T
This generates a format file for you for native format. Then you can edit
the format file to exclude the column you don't want to include. No need
for view or queryout.
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
Assuming that I have to use a Format File with bcp to export a table that
has a column I don't want exported.
What Format File should I create in order to achive the following two goals:
I wish to create the smallest data file possible, and would be the fastest
for the server to process in both directions (out/in).
Can you provide the syntax to generate the format file?
Thanks
Russell Mangel
Las Vegas, NVRussell Mangel,
Check arguments -n and -N in BOL.
AMB
"Russell Mangel" wrote:
> Before answering this question please read my previous post 10 minutes ago
.
> Assuming that I have to use a Format File with bcp to export a table that
> has a column I don't want exported.
> What Format File should I create in order to achive the following two goal
s:
> I wish to create the smallest data file possible, and would be the fastest
> for the server to process in both directions (out/in).
> Can you provide the syntax to generate the format file?
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>|||Yeah, I made some tests.
The database I have, does not have any UniCode columns, and so when I export
using -n the data does get much smaller.
However, since I am transferring the data via FTP, I ran PKZIP on the data
files and discovered that it does a great job of compressing UniCode, so it
turns I will use -N switch anyway.
Thanks Again.
Russell Mangel
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BFF9DF60-13D3-48CC-A7FA-979B23DA0BD4@.microsoft.com...
> Russell Mangel,
> Check arguments -n and -N in BOL.
>
> AMB
>
> "Russell Mangel" wrote:
>|||Russell Mangel (russell@.tymer.net) writes:
> Before answering this question please read my previous post 10 minutes
> ago.
> Assuming that I have to use a Format File with bcp to export a table that
> has a column I don't want exported.
> What Format File should I create in order to achive the following two
> goals:
> I wish to create the smallest data file possible, and would be the fastest
> for the server to process in both directions (out/in).
> Can you provide the syntax to generate the format file?
No, not without the table definition.
The fast format is native format, which means that you need to describe
each column correctly. This is a little more tricky that describing a
character format. Then again, anything goes, so if you have a mix of
native and charcter format that is not much of a distaster.
You will probably find it easier to use a view together as I suggested in my
other post together with the -n or -N option as suggested by Alejandro.
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|||[posted and mailed]
Erland Sommarskog (esquel@.sommarskog.se) writes:
> Russell Mangel (russell@.tymer.net) writes:
> No, not without the table definition.
I spoke to soon! I forgot about the format option. You can do this:
bcp Northwind..Orders format dummy.txt -f Orders.fmt -N -T
This generates a format file for you for native format. Then you can edit
the format file to exclude the column you don't want to include. No need
for view or queryout.
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
订阅：
博文 (Atom)