Solved

How to make XML from SQL

Posted on 2012-04-11
4
240 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
  • 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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