sql to xml

What SQL platform are you using (Oracle, SQL Server, MySQL, etc)?  Different SQL implementations have different syntax and functions related to XML.

---

»bp

ms sql server

any helps?

Give this a try:

SELECT Year, Make, Model FROM CarTable FOR XML PATH ('Vehicle'), ROOT ('Vehicles');

Select allOpen in new window

---

»bp

Running out of time here tonight, but that should get you started,  Dig in to the link below for more options and examples.

FOR XML (SQL Server) | Microsoft Docs

---

»bp

i copied your query and but now the row header showing the some name. So i think the best is how can I actually export to a file like abc.xml

and there is one big issues from all your guys query.
The xml format is not correct. The open and closing tag seems to have some space issue.
and I check my table, the space does not exist.
7-27-2017-12-43-25-PM.png

Please review my screenshot and you will see what's go wrong

@ITsolutionWizard,

In the sample output at ID: 42232467, there is no space issue. I do not see one. It seems, what you call space issue, is just text wrapping issue.

One simple way of creating xml document from an output like this is to copy and paste it notepad and save it as MyTestDon.xml

Also, you can use the following to store it in a variable:

DECLARE @X xml
Set @X= (
SELECT * FROM @Inventory
FOR XML raw('Vehicle'), root('Vehicles'), ELEMENTS)

SELECT @X

Select allOpen in new window

Are there some missing data in your table? If so, use the one below to insert ...xsi:nil="true"... where applicable  

DECLARE @X xml
Set @X= (
SELECT * FROM @Inventory
FOR XML raw('Vehicle'), root('Vehicles'), ELEMENTS XSINIL)

SELECT @X

Select allOpen in new window

See my 2.png and you can see why it is not working. I can actually do the output to a file using visual studio 2015 but the issue still occurred.
so now no matter i do copy / paste or export to xml. The output result is the same.
2.png

...RSV MILLE</M            <-- line 1, end
oldel></Vehicle>...        <-- line 2 begining

It seems Model is broken into M and odel because of word wrapping setting. How this is problem. I trust when you say it is a problem but because of lack of access, I am failing to comprehend it.

so how we can resolve that?

My question was, how is it a problem? Could you increase the word wrapping width in the document you are viewing the xml? I don't see how having blank space is a problem. What kind of problem are you having because of the space as you call it? A possible solution (increasing word-wrap problem) could be similar to Windows NotePad setting as shown in the image below where "Word Wrap" needs to be unchecked. Also, your application may have language options that is not set to XML yet.


The solution below is giving you the xml structure you are asking for. The syntax below is correct. It handshakes with your data and produces what you are asking for:

FOR XML raw('Vehicle'), root('Vehicles'), ELEMENTS XSINIL)

1. Vehicle: Element tag name.
2. root('Vehicles'): Root element tag name
3. ELEMENTS : Display data in element tags
4. XSINIL: Don't escape element tag if the data is missing, show the tags with no data in it.

So, we are limited to the knowledge on how to compose the above 4 parts to produce what you are asking for. The other side of the deal is your data quality. It maybe that Microsoft has problem with their XML codes and we need possibly to communicate to them. Or, you need to set your document-er application's language setting to xml or work with its wrapping setting.  

Thanks,

Mike

Edited... 7/29/2017 7:00 PM