Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-09
46
Medium Priority
?
1,596 Views
Last Modified: 2012-05-05
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
Comment
Question by:Gudorian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 24
  • 19
  • 3
46 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22689681
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22692841
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22693612
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22693762
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22694253
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22697482
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22700297
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22700308
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22700382
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 22707671
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22720883
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22721379
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22721585
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22721644
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22721645
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22721675
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22721720
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22721984
No.  Spaces in tags are ok.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22722234
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22728167
Here's the result returned from both the functions.



results.zip
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22728257
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22728271
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22728283
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22729891
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22730270
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22730320
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22730327
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22730424
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 2000 total points
ID: 22730441
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22730481
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22730568
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22730622
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22730624

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
 
LVL 2

Author Comment

by:Gudorian
ID: 22731148
Then I got the result as in the file posted

DuellsCM5.dbo.uget-artikels-tabl.txt
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22731272
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22731581
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22731752
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22731848
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22731933
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22732067
It's the same outputting it just to C:\

and after creating C:\ee
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22732203
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22732296
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 2000 total points
ID: 22732428
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
 
LVL 2

Author Comment

by:Gudorian
ID: 22732566
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
 
LVL 2

Author Closing Comment

by:Gudorian
ID: 31504921
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22732664
Pleasure, glad we got to the bottom of it all...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question