Solved

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

Posted on 2008-10-09
46
1,543 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
  • 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
 
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 500 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 500 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 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now