Solved

How to make XML from SQL

Posted on 2012-04-11
4
245 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article shows a few slightly more advanced techniques for Windows 7 gadget programming, including how to save and restore user settings for your gadget and how to populate the "details" panel that is displayed in the Windows 7 gadget gallery.  …
This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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