[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to make XML from SQL

Posted on 2012-04-11
4
Medium Priority
?
252 Views
Last Modified: 2012-05-31
Hi!

Have a table, that contains of many fields..

Must create a stored procedure, that has @param1
as the variable to use in my Select query like ->

Select * from table where name=@param1
....

The result of the query must build a XML-file
Where the name of the XML is set to ->@param1.XML
and must be stored like -> C:\test\123000A.XML (@param1 = 123000A)

All the tags in the XML-file must be set in the stored procedure
So the tags are not the same as the fieldnames in the table

Example:

Table

Name    Varchar(30)
tlf Varchar(20)
mob   Varchar(20)
status Varchar(2)
number int

<navnet>Name</navnet>
<telefon>tlf</telefon>
...
...

Must be able to set the title of the XML to


How can i do this
0
Comment
Question by:team2005
[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
  • 3
4 Comments
 
LVL 12

Accepted Solution

by:
Anuradha Goli earned 1500 total points
ID: 37832376
To output your query as XML

Select Name,
tlf as telefon,
mob as mobile,
.................
from tablename
for XML RAW;

To Save your SELECT query results to XML File or a text file, use this query :

--For XML

EXEC master..xp_cmdshell'bcp "Select Name,tlf as telefon,mob as mobile,.................from tablename FOR XML PATH (''e''), ROOT(''emp'')" queryout "c:\text.xml" -c -T -x'

instead of text.xml you can use your parameter ,as string and execute to perform the action.
0
 
LVL 2

Author Comment

by:team2005
ID: 37836296
Hi!

Tryed this:

Select kjedenummer as KJEDENR,kjedenavn as NAVN,Datoinn as REGDATO
from dbo.KJE_KJEDE
for XML RAW;

EXEC master..xp_cmdshell'bcp "Select kjedenummer as KJEDENR,kjedenavn as NAVN,Datoinn as REGDATO 
from dbo.KJE_KJEDE FOR XML PATH (''e''), ROOT(''emp'')" queryout "c:\text.xml" -c -T -x'

Open in new window


It output this:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
(null)

What is wrong ?
0
 
LVL 2

Author Comment

by:team2005
ID: 37854597
Hi!

Please sombody help me with this issue ?
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38032341
thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

656 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