?
Solved

How to make XML from SQL

Posted on 2012-04-11
4
Medium Priority
?
249 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

771 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