Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1609
  • Last Modified:

SQL to XML - Get value as <value> instead of <this>value</this>

Hi,

Is it possible to get the value returned from the sql query to be inside <value></value> ?

I want the value returned from c.[name] to be inside of <c.[name]> </c.[name]>

and values of 'b.ART_ARTNR' and 'd.val' inside of 'c.[name]' like this.

<c.[name]>
   <Artikelnr>b.ART_ARTNR</Artikelnr>
   <Value>d.val</Value>
 </c.[name]>

And also I would like a.rek_id and a.rek_name to only repeat once like

<RektabellContent id="a.rek_id" name="a.rek_name">

<Rektabell>
<c.[name]>
   <Artikelnr>b.ART_ARTNR</Artikelnr>
   <Value>d.val</Value>
 </c.[name]>

<c.[name]>
   <Artikelnr>b.ART_ARTNR</Artikelnr>
   <Value>d.val</Value>
 </c.[name]>

<c.[name]>
   <Artikelnr>b.ART_ARTNR</Artikelnr>
   <Value>d.val</Value>
 </c.[name]>

</Rektabell>

</RektabellContent>

if it's any help, this is how the results looks as it is now:

--------

<RektabellContent>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning ESS<Artikelnr>74-158336</Artikelnr><Value>Motor Kimpex för snöslunga</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning <Artikelnr>74-158336</Artikelnr><Value>Motor Kimpex</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Artikeltext<Artikelnr>74-158336</Artikelnr><Value>För snöslunga 13 hk, fyrtakt</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Enhet<Artikelnr>74-158336</Artikelnr><Value>stk</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning ESS<Artikelnr>74-058125</Artikelnr><Value>Borste roterande Kimpex för ATV</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning <Artikelnr>74-058125</Artikelnr><Value>Roterande borste, Kimpex</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Enhet<Artikelnr>74-058125</Artikelnr><Value>stk</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning ESS<Artikelnr>74-058122</Artikelnr><Value>Snöslunga Kimpex pioneer för ATV</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning <Artikelnr>74-058122</Artikelnr><Value>Snöslunga Kimpex Pioneer</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Enhet<Artikelnr>74-058122</Artikelnr><Value>stk</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning ESS<Artikelnr>62-625</Artikelnr><Value>Glasögon Elite Elan svart</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning <Artikelnr>62-625</Artikelnr><Value>Glasögon</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Enhet<Artikelnr>62-625</Artikelnr><Value>stk</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning ESS<Artikelnr>92-720</Artikelnr><Value>Spännband 0.3m + 4.7m  krok/krok</Value></Artikel>
  </Rektabell>
  <Rektabell id="1" name="Drivpaket">
    <Artikel>Benämning <Artikelnr>92-720</Artikelnr><Value>Spännband 1000 kg</Value></Artikel>
  </Rektabell>
</RektabellContent>

--------
DECLARE @rek_id int;
SET @rek_id = 1;
 
SELECT
 
a.rek_id AS '@id', 
a.rek_name AS '@name',
c.[name] AS 'Artikel',
b.ART_ARTNR AS 'Artikel/Artikelnr',
d.val AS 'Artikel/Value'
 
FROM _Rektabell AS a
 
INNER JOIN _rek2art AS r2a
ON r2a.rek_id = @rek_id
 
INNER JOIN ARTIKEL AS b
ON b.art_id = r2a.art_id
 
INNER JOIN ARTIKEL_Details AS d
ON b.ART_ARTNR = d.ARTNR
 
INNER JOIN ARTIKEL_Fields AS c
ON c.id_field = d.id_field
 
WHERE a.rek_id = @rek_id
 
FOR XML PATH ('Rektabell'),
ROOT('RektabellContent');

Open in new window

0
Gudorian
Asked:
Gudorian
  • 24
  • 19
  • 3
3 Solutions
 
BrandonGalderisiCommented:
I believe removing @ from (a.rek_name AS '@name',) will do that:
DECLARE @rek_id int;
SET @rek_id = 1;
 
SELECT
 
a.rek_id AS '@id', 
a.rek_name AS 'name',
c.[name] AS 'Artikel',
b.ART_ARTNR AS 'Artikel/Artikelnr',
d.val AS 'Artikel/Value'
 
FROM _Rektabell AS a
 
INNER JOIN _rek2art AS r2a
ON r2a.rek_id = @rek_id
 
INNER JOIN ARTIKEL AS b
ON b.art_id = r2a.art_id
 
INNER JOIN ARTIKEL_Details AS d
ON b.ART_ARTNR = d.ARTNR
 
INNER JOIN ARTIKEL_Fields AS c
ON c.id_field = d.id_field
 
WHERE a.rek_id = @rek_id
 
FOR XML PATH ('Rektabell'),
ROOT('RektabellContent');

Open in new window

0
 
GudorianAuthor Commented:
Have tested that, but it just makes it go like <name>a.rek_name</name>
I want it the other way around like <a.rek_name>other values</a.rek_name>
0
 
Mark WillsTopic AdvisorCommented:
You would normally put the sub-group items in a sub-query...  and have that as a type - and if not a sub-query at the very least belonging to c.[name]

will try to decipher the query example above and see if I can make the query work...
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Mark WillsTopic AdvisorCommented:
Might need some sample data from you...

In the meantime, have a look at :



DECLARE @rek_id int;
SET @rek_id = 1;
 
SELECT
 
a.rek_id AS '@id', 
a.rek_name AS '@name',
(
select
c.[name] as '@name',
b.ART_ARTNR AS 'Artikelnr',
d.val AS 'Value'
 
FROM _Rektabell AS aa
 
INNER JOIN _rek2art AS r2a
ON r2a.rek_id = aa.rek_id
 
INNER JOIN ARTIKEL AS b
ON b.art_id = r2a.art_id
 
INNER JOIN ARTIKEL_Details AS d
ON b.ART_ARTNR = d.ARTNR
 
INNER JOIN ARTIKEL_Fields AS c
ON c.id_field = d.id_field
 
where aa.rek_id = a.rek_id
 
FOR XML path('Artikel'), type) as rektabell
 
FROM _Rektabell AS a
 
WHERE a.rek_id = @rek_id
 
FOR XML PATH ('RektabellContent'),type 
;

Open in new window

0
 
GudorianAuthor Commented:
I have to take a closer look at monday when I get back to work,

Tell me what sample data you need and I'll provide it for you.


0
 
Mark WillsTopic AdvisorCommented:
A few rows from each of the tables would be handy...

Oh, and to answer your original question... The tag for grouping by artikel name (ie c.[name]) is not really regarded as a legitimate tag. Hence you will see in the example above an extended tag of <artikel name = c.name>. The tags can be anything really and truely, but normally represent field names not field contents, could be difficult to unpack at the other end...
0
 
GudorianAuthor Commented:
Really hope there's a way.

I have put out the data from the tables involved in this and put it to .csv files.

I have also made a db diagram and put it in a jpeg.
0
 
GudorianAuthor Commented:
And here's the file :)

It complained on the file extensions inside the zip so made the .csv files to .txt files

csv.zip
0
 
Mark WillsTopic AdvisorCommented:
There is always a way, just comes down to coding and effort... It is not uncommon to resort to a User Defined Function to manually format how you want it to look. It is just that the recipient will have to know how to handle data elements if used as tags. Will look at the ZIP and get back to you soon.
0
 
Mark WillsTopic AdvisorCommented:
OK, have a look at the various samples below...

open a query window and past the contents below into that query window.

Please note, it does not do any character manipulation like replacing special characters in every situation such as:

            SET @Value = replace(@Value,'&','&amp;')
            SET @Value = replace(@Value,'"','&quot;')
            SET @Value = replace(@Value,'<','<')
            SET @Value = replace(@Value,'>','>')

which you may need to do for each of the variables before using them...  Also, you may need to check the data types.

read the comments, then run each "section" by highlighting the code between the comments and pressing F5.


--
-- The very manual way of forcing an XML format when all else fails is to write code...
-- We do this by creating a function to output formatted XML compliant strings using @rek_ID as the parameter for the function.
-- Example 1 function uses a table - assuming export to disk (see further down)
-- Example 2 function uses a string variable
--
-- Function - Example 1 - Output as table (good for export to disk)
--
create function uGet_Artikels_Table (@rek_id int)
returns @tbl table (xmlstring nvarchar(500))
as
begin
 
declare @id int
declare @rek_name nvarchar(120)
declare @field_name nvarchar(120)
declare @art_artnr nvarchar(120)
declare @value nvarchar(120)
 
declare @last_id int
declare @last_field nvarchar(120)
 
declare c cursor read_only for SELECT a.rek_id, a.rek_name, c.[name], b.ART_ARTNR,d.val
                               FROM _Rektabell AS a
                               INNER JOIN _rek2art AS r2a ON r2a.rek_id = a.rek_id
                               INNER JOIN ARTIKEL AS b ON b.art_id = r2a.art_id
                               INNER JOIN ARTIKEL_Details AS d ON b.ART_ARTNR = d.ARTNR
                               INNER JOIN ARTIKEL_Fields AS c ON c.id_field = d.id_field
                               WHERE a.rek_id = @rek_id
 
open c
fetch next from c into @id, @rek_name, @field_name, @art_artnr, @value
while @@fetch_status = 0
begin
 
   set @Value = replace(@Value,'&','&amp;')
   set @Value = replace(@Value,'"','&quot;')
   set @Value = replace(@Value,'<','&lt;')
   set @Value = replace(@Value,'>','&gt;')
 
   if isnull(@last_id,9999) <> @id   
   begin
      insert @tbl values ('<RektabellContent id="'+convert(nvarchar,@id)+'" name="'+convert(nvarchar,@rek_name)+'">')
      insert @tbl values ('   <Rektabell>')
   end
 
   if isnull(@last_field,'xxxx') <> 'xxxx'
      insert @tbl values ('      </'+convert(nvarchar,@last_field)+'>')
 
   if isnull(@last_field,'xxxx') <> @field_name
      insert @tbl values ('      <'+convert(nvarchar,@field_name)+'>')
 
   insert @tbl values ('         <Artikelnr>'+convert(nvarchar,@art_artnr)+'</Artikelnr>')
   insert @tbl values ('         <Value>'+convert(nvarchar,@value)+'</Value>')
 
   set @last_id = @id
   set @last_field = @field_name
 
   fetch next from c into @id, @rek_name, @field_name, @art_artnr, @value
 
end
 
if isnull(@last_id,9999) = @id
begin
   insert @tbl values ('      </'+convert(nvarchar,@last_field)+'>')
   insert @tbl values ('   </Rektabell>')
   insert @tbl values ('</RektabellContent>')
end
 
close c
deallocate c
return
end
GO
 
--
-- now output using Example 1 function it to an external file 
-- note : this bcp command example uses "trusted" authentication (ie -T), might need -U<login> -P<password> instead of -T
-- please change <mydb> to whatever your database is...
--
declare @rek_id int
declare @bcpcommand varchar(200)
 
set @rek_id = 1
set @bcpcommand = 'bcp "select * from <mydb>.dbo.uget_artikels_table('+convert(varchar,@rek_id)+')" queryout "c:\ee\artikels.xml" -T -c -CACP'
exec master..xp_cmdshell @bcpcommand
 
GO
--
-- Now go  have a look on the server for C:\artikels.xml (open in notepad first)
-- or you could just do select * from dbo.uget_artikels_table(1)
 
--
-- Function - Example 2 - Output as string - good for further manipulations in SQL
--
create function uGet_Artikels_String (@rek_id int)
returns varchar(max)
as
begin
 
declare @id int
declare @rek_name nvarchar(120)
declare @field_name nvarchar(120)
declare @art_artnr nvarchar(120)
declare @value nvarchar(120)
 
declare @last_id int
declare @last_field nvarchar(120)
declare @str varchar(max)
set @str = ''
 
declare c cursor read_only for SELECT a.rek_id, a.rek_name, c.[name], b.ART_ARTNR,d.val
                               FROM _Rektabell AS a
                               INNER JOIN _rek2art AS r2a ON r2a.rek_id = a.rek_id
                               INNER JOIN ARTIKEL AS b ON b.art_id = r2a.art_id
                               INNER JOIN ARTIKEL_Details AS d ON b.ART_ARTNR = d.ARTNR
                               INNER JOIN ARTIKEL_Fields AS c ON c.id_field = d.id_field
                               WHERE a.rek_id = @rek_id
 
open c
fetch next from c into @id, @rek_name, @field_name, @art_artnr, @value
while @@fetch_status = 0
begin
 
   set @Value = replace(@Value,'&','&amp;')
   set @Value = replace(@Value,'"','&quot;')
   set @Value = replace(@Value,'<','&lt;')
   set @Value = replace(@Value,'>','&gt;')
 
   if isnull(@last_id,9999) <> @id
   begin
      set @str = @str + '<RektabellContent id="'+convert(nvarchar,@id)+'" name="'+convert(nvarchar,@rek_name)+'">'
      set @str = @str + '   <Rektabell>'
   end
 
   if isnull(@last_field,'xxxx') <> 'xxxx' 
      set @str = @str + '      </'+convert(nvarchar,@last_field)+'>'
 
   if isnull(@last_field,'xxxx') <> @field_name
      set @str = @str + '      <'+convert(nvarchar,@field_name)+'>'
 
   set @str = @str + '         <Artikelnr>'+convert(nvarchar,@art_artnr)+'</Artikelnr>'
   set @str = @str + '         <Value>'+convert(nvarchar,@value)+'</Value>'
 
   set @last_id = @id
   set @last_field = @field_name
 
   fetch next from c into @id, @rek_name, @field_name, @art_artnr, @value
 
end
 
if isnull(@last_id,9999) = @id
begin
   set @str = @str + '      </'+convert(nvarchar,@last_field)+'>'
   set @str = @str + '   </Rektabell>'
   set @str = @str + '</RektabellContent>'
end
 
close c
deallocate c
return @str
end
GO
--
-- now output using Example 2 function
-- note : reason why it was a string is so you can manipulate as much as you want, can always convert to XML
-- similarly, the "return as" clause from the function could have returned XML instead of varchar(max)...
--
select convert(xml,dbo.uGet_Artikels_String(1))
 
--
-- So, that is that... Questions ?
--

Open in new window

0
 
GudorianAuthor Commented:
Sorry for the delay.

I get an error when running the last part of the query,

select convert(xml,dbo.uGet_Artikels_String(1))

like this

Msg 9414, Level 16, State 1, Line 2
XML parsing: line 1, character 77, equal expected

---

This is what I've done.

Created the functions, enabled xp_cmdshell and I entered the name of the database here

"...from <mydb>.dbo.uget_artikels_ta..."

Did I miss out on something?
0
 
Mark WillsTopic AdvisorCommented:
sounds correct. Might have a datatype or two the wrong way around... or, even should be nvarchar etc..

What happens if you just do:

select dbo.uGet_Artikels_String(1)     --where 1 should be a real rek_id number not just the example...

or,

select * from dbo.uget_artikels_table(rek_id)   -- where rek_id is a real number...
0
 
GudorianAuthor Commented:
For the first test it gets it all out the right way, but just in one row like below

<RektabellContent id="1" name="Drivpaket">   <Rektabell>      <Benämning ESS>         <Artikelnr>74-158336</Artikelnr>         <Value>Motor Kimpex för snöslunga</Value>      </Benämning ESS>      <Benämning >         <Artikelnr>74-158336</Artikelnr>         <Value>Motor Kimpex</Value>      </Benämning >      <Artikeltext>         <Artikelnr>74-158336</Artikelnr>         <Value>För snöslunga 13 hk, fyrtakt</Value>      </Artikeltext>      <Enhet>         ......

and the second query gets it like this, one row, much like a normal query

1. <RektabellContent id="1" name="Drivpaket">
2.  <Rektabell>
3.      <Benämning ESS>
4.         <Artikelnr>74-158336</Artikelnr>
5.         <Value>Motor Kimpex för snöslunga</Value>
6.      </Benämning ESS>
7.      <Benämning >
8.         <Artikelnr>74-158336</Artikelnr>
9.         <Value>Motor Kimpex</Value>
10.     </Benämning >
0
 
BrandonGalderisiCommented:
That's because one is building an XML string and the other is selecting the values out as a table.  As indicated by the function names.

If you return XML like this, it will be returned as a single value.  XML doesn't care about making it pretty (nested and tabbed), it just cares about structure.

no points for this, just explaining.
0
 
GudorianAuthor Commented:
Also, I tried to copy the result and pasted in a XML header:

<?xml version="1.0" encoding="iso-8859-1" ?>

and then did an import to MS Excel.

It reported that it couldn't find the xml schema referenced to.

Mayby it's somewhere it goes wrong outputting so sql server can't convert it to xml?


0
 
GudorianAuthor Commented:
Could it have something to do with this:

<Benämning >
         <Artikelnr>74-058122</Artikelnr>
         <Value>Snöslunga Kimpex Pioneer</Value>
      </Benämning >

?

after <Benämning[ ]> there's a whitespace.
0
 
GudorianAuthor Commented:
Almost spam here...


Tested to remove the whitspaces and now it works in excel with the result returned.  Is it possible to modify the  sql function to remove Whitespaces at the end?
0
 
BrandonGalderisiCommented:
No.  Spaces in tags are ok.
0
 
Mark WillsTopic AdvisorCommented:
Don't worry about the header...

Can you zip and post the results - the table one is good for the BCP export....

The "string one" should return a blue underline single cell on the results tab if running interactively in SSMS query window. Simple double click on it and it should open in a new window - nicely formatted.

XML is really just a big long string, it does not have to be record by record - but that is where the table version works out pretty well... It will output row by row.

The "challenge" with extended ascii characters is that they might not always convert. That trailing space whilst should not be a proble, might not be a trailing space. So best to look at it if possible.
0
 
GudorianAuthor Commented:
Here's the result returned from both the functions.



results.zip
0
 
Mark WillsTopic AdvisorCommented:
Yep, does not like :

1) Special Characters ie ä
2) Embedded blanks in the Tag Name

What do you want to do ? Can change special characters, and can remove embedded blanks...
0
 
GudorianAuthor Commented:
sry, missed to run the bcp command. Having an error here aswell.

gets:

1. SQLState = HY000, NativeError = 0
2. Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
3. NULL





If I got it right, "trusted authentication" is the same as "Windows authentication"?

or I got it wrong here?

Open in new window

0
 
GudorianAuthor Commented:
Characters like "ä, ä, ö" can be used if having a charset like "iso-8859-1" or running "utf-8" with "BOM".

but the blanks at the end I would be happy to somehow get removed on run.
0
 
Mark WillsTopic AdvisorCommented:
Yes, understand the iso standards, and will / can include <?xml version="1.0" encoding="iso-8859-1" ?> as opening line...
but, the embedded blanks - not just trailing blanks - also cause a problem, We can substitue for an Underscore, and leave it for the unpack procedure to replace that underscore , but so far - and have spent a few hours on it, have not been able to get it to obey the embedded blanks - including namespace, entity mapping and a few other advanced techniques (along with an xls template)
0
 
GudorianAuthor Commented:
I think the best way is to replace the blanks with underscore.

I've seen a function that's

Replace('this', 'something', 'with_this')

or it might not work,
0
 
Mark WillsTopic AdvisorCommented:
It will work, no worries about that - and if the embedded spaces are required, then you will have to revert to named tags (as per the "for XML" example originally posted).

Now, before we do that, looking at the sample data, we should really sort the input so that the <c.[name]> tag is not being repeated each time - or is that what you want ?

Also, can you tell me a little more about those errors ? Assume the table export worked ok (it was in the zip file), but is there anything else we have to address ?
0
 
GudorianAuthor Commented:
It worked doing

if isnull(@last_field,'xxxx') <> 'xxxx'
      insert @tbl values ('      </'+replace(convert(nvarchar,@last_field), ' ', '_')+'>')

now the blanks is being replaced with an underscore,

it's still not working but I'm guessing it's becouse of the charset is set wrong. this I have no idea of how to do
0
 
GudorianAuthor Commented:
This is the erros messages:

Function1:

select convert(xml,dbo.uGet_Artikels_String(1))

Error1:

Msg 9414, Level 16, State 1, Line 2
XML parsing: line 1, character 77, equal expected

Function2:

declare @rek_id int
declare @bcpcommand varchar(200)
 
set @rek_id = 1
set @bcpcommand = 'bcp "select * from DuellsCM5.dbo.uget_artikels_table1('+convert(varchar,@rek_id)+')" queryout "c:\ee\artikels.xml" -T -c -CACP'
exec master..xp_cmdshell @bcpcommand

Error2:

1. SQLState = HY000, NativeError = 0
2. Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
3. NULL

It would be great if the charset was included at top.

The embedded spaces could be underscores.

I think the output as it is now is perfect, so no need to change the <c.[name]> tag :)
0
 
Mark WillsTopic AdvisorCommented:
Yes, you need <?xml version="1.0" encoding="iso-8859-1" ?>

as the opening line...

In the table version of the function, simply insert that line as the very first line in the table - in fact as a line above the very first insert, and similarly the same with the string version..:


      insert @tbl values ('<?xml version="1.0" encoding="iso-8859-1" ?>')
      insert @tbl values ('<RektabellContent id="'+convert(nvarchar,@id)+'" name="'+convert(nvarchar,@rek_name)+'">')


      set @str = @str + '<?xml version="1.0" encoding="iso-8859-1" ?>'
      set @str = @str + '<RektabellContent id="'+convert(nvarchar,@id)+'" name="'+convert(nvarchar,@rek_name)+'">'
0
 
Mark WillsTopic AdvisorCommented:
OK, the select convert to XML will be the formatting of the XML string. The new heading line should take care of that...

The BCP process for the table version - are you sure it is spelled correctly ? seems to have a rougue 1 in there :

select * from DuellsCM5.dbo.uget_artikels_table1(....)   should be:  select * from DuellsCM5.dbo.uget_artikels_table(....)

And yes the -T is essentially windows authentication (ir Trusted connection).
0
 
GudorianAuthor Commented:
Added the xml header for both the functions.

The return as String function now works perfectly fine,

but table function still reports the same error as posted above.
0
 
GudorianAuthor Commented:
Have made a copy of  
"uget_artikels_table"

called

"uget_artikels_table1"

So shouldn't be a misspell. same error when running

"uget_artikels_table" without "1"
0
 
Mark WillsTopic AdvisorCommented:

OK let's try it manually first, and also check that output path...


select * from DuellsCM5.dbo.uget_artikels_table(1)     --where 1 is a legitimate rek_id...
0
 
GudorianAuthor Commented:
Then I got the result as in the file posted

DuellsCM5.dbo.uget-artikels-tabl.txt
0
 
Mark WillsTopic AdvisorCommented:
OK, so it is not the procedure as such, it is BCP logging in...


so, let's go to the next step.... first trying a "real" sql login user - maybe even sa


exec master..xp_cmdshell 'bcp "select * from DuellsCM5.dbo.uget_artikels_table(1)" queryout "C:\test.cml" -U<username> -P<password> -c -CACP'


0
 
GudorianAuthor Commented:
ok, tried with this and got a new error :)

1. The system cannot find the file specified.
2. NULL

Do I have to create a file at that pos or is it possible to make it creat one, mayby with diffrent names?
0
 
Mark WillsTopic AdvisorCommented:
No, it should create it automatically... Would have been better with an extension of xml, but, that should not be a real problem...

OK, take one more step back:

exec master..xp_cmdshell 'dir c:\*.*'
0
 
GudorianAuthor Commented:
I changed the extension to .xml, mayby I shouldn't?

exec master..xp_cmdshell 'bcp "select * from DuellsCM5.dbo.uget_artikels_table(1)" queryout "C:\ee\artikels.xml" -U<XXXX> -P<XXXX> -c -CACP'


This is the output for the other query:


  1. Volume in drive C has no label.
  2. Volume Serial Number is 20BA-B131
  3. NULL
  4. Directory of c:\
  5. NULL
  6. 2008-04-16  16:05       170 277 376 20080416.bak
  7. 2008-08-29  15:16                 0 AUTOEXEC.BAT
  8. 2008-09-26  11:57        93 407 744 CM5_backup.bak
  9. 2008-08-29  15:16                 0 CONFIG.SYS
10. 2008-09-01  15:08    <DIR>          Documents and Settings
11. 2008-09-01  14:48    <DIR>          FPSE_search
12. 2008-09-01  14:48    <DIR>          Inetpub
13. 2008-09-01  15:13    <DIR>          Program Files
15. 2008-09-01  14:56    <DIR>          WINDOWS
16. 2008-08-29  15:16    <DIR>          wmpub
17.               4 File(s)    263 685 120 bytes
18.               6 Dir(s)  19 271 151 616 bytes free
19. NULL
0
 
Mark WillsTopic AdvisorCommented:
OK I see the problem, you still have the path C:\EE so you will need to either remove the path (as per my example :)) or, create that folder...
0
 
GudorianAuthor Commented:
It's the same outputting it just to C:\

and after creating C:\ee
0
 
Mark WillsTopic AdvisorCommented:
This is very frustrating... Must be something simple...

Please try :

exec master..xp_cmdshell 'Dir c:\*.* >>  c:\ee\ee.txt'

then have a look using windows explorer in c:\ee and see if ee.txt exists...
0
 
GudorianAuthor Commented:
indeed,

It put's the txt file in C:\ee\ee.txt,

the content of the file is the same as when outputting it with

exec master..xp_cmdshell 'dir c:\*.*'
0
 
Mark WillsTopic AdvisorCommented:
OK, then we can run the procedure interactively, we can write to C:\ee, but there is a "disconnect" when bcp tries to...

Is the stored procedure that worked interactively the one with or without a 1 ?

exec master..xp_cmdshell 'bcp "select * from DuellsCM5.dbo.uget_artikels_table(1)" queryout C:\ee\art_test2.xml -Usa -Ppassword -c -CACP'
0
 
GudorianAuthor Commented:
Tada :D it works,

Thank you so much for all your help, I'm truly greatfull(?)...

and now to my next problem... just kidding, thx again ;)
0
 
GudorianAuthor Commented:
Excellent help, thx for not giving up on me :)

Easy to understand and also if I need I can modify it without it being to complex.
0
 
Mark WillsTopic AdvisorCommented:
Pleasure, glad we got to the bottom of it all...
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 24
  • 19
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now