I want create a XML File using the T-sql for below format

Question

I want create a XML File using the T-sql for below format

unnati.patel513

Say Hey Kid

Points: 697
More actions
December 19, 2011 at 10:08 am

#265200

Hi,
I want to create xml file using T-sql.. XML file is below format.
please help me how i can create the xml file..
<ns3:mapping xmlns:ns3="http://cgt.exmple.net/20101114/xyz/genericmap">
<ns3:field>
<generic>custID</generic>
<specific>custID</specific>
</ns3:field>
<ns3:field>
<generic>Address</generic>
<specific>Address</specific>
</ns3:field>
</ns3:mapping>
what are the step i have do for this..
Thank you in Advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

T-SQL can't, by itself, create a file. It can query data in XML format, but it can't create a file directly.

If this is a one-time operation, then you can save the results of a query as a file pretty easily in a number of different ways, including copy-and-paste, save as, or Results to File.

If you want something automated, then you might want to look into bcp. I'd go for SSIS, personally.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

unnati.patel513 Say Hey Kid Points: 697 More actions · Answer 2

HI

Yes i want query data in XML format..

i want something like you have column name is 'subscription'

in that column you store some xml link..

and when you open that link from column it will open that xml format..

E.g

Custid Adress Subcription

1 USA ns3:mapping xmlns:ns3="http://chj.example.net/20101114/XYZ/genericmap

2 London ns3:mapping xmlns:ns3="http://chj.example.net/20101114/XYZ/genericmap

3 Brazil ns3:mapping xmlns:ns3="http://chj.example.net/20101114/XYZ/genericmap

the table is look like above and when you click the any value of subsription it will open the that xml format which i allready mention..

and the datatype for subsription column is xml(.)

Please help me how i do coding so for this issues

Thank you..

GSquared SSC Guru Points: 260824 More actions · Answer 3

I don't understand your question/request/post.

Are you trying to store XML in a database? If so, you just define a table with a column that has the XML datatype, define an XSD for it (or more than one), and go from there.

But you don't generally "click on a column" in a database. A database is just a storage medium for data, it isn't really "clickable". That's going to be done via some sort of user interface. Maybe a web page, maybe a report (web or e-mail), maybe an application like Word or Excel or something custom-built in .NET or Java or whatever.

Are you asking me to program an interface for you? If so, I'm not the right person to ask for that from. You'll need to hire a developer who does contract work for that kind of thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

drew.allen SSC Guru Points: 76985 More actions · Answer 4

He's confusing the UI (SSMS) with the database. SSMS will display XML results as clickable text when using the grid display.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

unnati.patel513 Say Hey Kid Points: 697 More actions · Answer 5

yes i lookng for something like that.. when you click value in column it will open the xml coding in SSMS..

Thank you..

drew.allen SSC Guru Points: 76985 More actions · Answer 6

unnati.patel513 (12/20/2011)
yes i lookng for something like that.. when you click value in column it will open the xml coding in SSMS..

This will happen automatically in SSMS for anything that's XML. If you can't click on it, then it must be some other datatype (probably nvarchar or varchar). You can use the CAST or CONVERT functions to convert other datatypes to XML or you can use the FOR XML clause to convert the whole resultset of a query or sub-query to XML.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

unnati.patel513 Say Hey Kid Points: 697 More actions · Answer 7

Hi,

Thank you very much i got my answer.. i have to define the datatype as xml(.) and just insert xml values in that perticular column.

i appreciated for your response..

Thank you once again..