Link to home
Start Free TrialLog in
Avatar of Gudorian
GudorianFlag for Sweden

asked on

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

Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image
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

Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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>
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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...
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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

Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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.


Avatar of Mark Wills
Mark Wills
Flag of Australia image
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...
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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.
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

And here's the file :)

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

csv.zip
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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?
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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...
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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 >
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image
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.
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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?


Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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.
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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?
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image
No.  Spaces in tags are ok.
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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.
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

Here's the result returned from both the functions.



results.zip
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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...
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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

Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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.
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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)
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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,
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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 ?
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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 :)
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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).
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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.
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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"
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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...
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

Then I got the result as in the file posted

DuellsCM5.dbo.uget-artikels-tabl.txt
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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'


Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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?
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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:\*.*'
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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...
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

It's the same outputting it just to C:\

and after creating C:\ee
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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...
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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:\*.*'
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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 ;)
Avatar of Gudorian
Gudorian
Flag of Sweden image

ASKER

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.
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Pleasure, glad we got to the bottom of it all...