April 4, 2008 at 11:04 am
Hi,
I have SP that generates xml from database and it works great, but when I try to run it using bcp it errors out. Error is: Native Client UNAble to open BCP host data-file
my command is: bcp "Exec njdb.dbo.xmlExportSP" queryout D:\export\google.xml -S***** -U****** -P***** -c -r -t
????
April 4, 2008 at 11:14 am
I've had trouble using BCP with procs, if I remember correctly. May not work that way.
Books Online has an article on importing/exporting XML at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm
Check that out and it will probably answer your question for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2008 at 1:18 pm
I got it figured out, i misspelled the folder name.
Another issue thou, didn't see it before cuz I was selecting only one record.
This is my SP:
WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)
SELECT
TITLE"item/title",
description"item/description",
employer"item/g:employer",
job_type"item/g:job_type",
link"item/link",
location"item/g:location",
salary"item/g:salary",
education"item/g:education"
FROM JobExport
FOR XML PATH ('chanel'), ROOT('rss')
I need to get xml formated like this:
- rss version="2.0" xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0"
- channel
+ item>...</item
+ item>....</item
/channel>
/rss>
but instead I am getting this:
- rss version="2.0" xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0"
- channel
+ item>...</item
/channel>
- channel
+ item>....</item
/channel>
/rss>
ps. I wont let me post xml so I had to remove some tags off it
April 4, 2008 at 2:40 pm
I am not sure why you are recieving your output like that. You XML looks good and I even took your sample and made a quick runthrough and everything looked good. It may have something to do with the way the data is being returned from your query. Make sure the query is producing the correct result, without for xml path.
Also, you can post xml of this site, but the trick is you have to use the html equivilant & lt; and & gt;. Note I have supplied a space between & and the letters, but it should not be included.
The sample:
WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)
SELECT
'title' "item/title",
'description' "item/description",
'employer' "item/g:employer",
'job_type' "item/g:job_type",
'link' "item/link",
'location' "item/g:location",
'salary' "item/g:salary",
'education' "item/g:education"
FOR XML PATH ('chanel'), ROOT('rss')
April 5, 2008 at 11:42 am
I assume that "chanel" is looping with the rest of the records, is there a way to set "chanel" to be a root of the xml just like "rss" is?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply