Solved

How to make XML from SQL

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now