sql to formatted xml - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql to formatted xml

Author

Topic

aneeshmm
Starting Member

20 Posts

Posted - 2009-01-29 : 05:56:53

i have a code to write my sql data to xml. current format is
<chart>
<set>
<label>abcd</label>
<value>abcd</value>
<set>
</chart>
i want to change it to following format
<chart>
<set label="abcd" value="abcd">
</chart>
any suggestions????
thnx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:06:52

May we see the original query producing the xml string above?
You have to change from elements to attributes.

E 12°55'05.63"
N 56°04'39.26"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-29 : 06:08:40

If I understand you correctly, you need to look at the FOR XML command and possibly use the EXPLICIT argument.

Peter: You're too quick for me..

aneeshmm
Starting Member

20 Posts

Posted - 2009-01-29 : 06:10:15

SqlConnection cnn = new SqlConnection("data source=.\\sqlexpress; integrated security=true ;initial catalog=test");
cnn.Open();
SqlDataAdapter ad = new SqlDataAdapter("select name as label,salary as value from employee", cnn);
cnn.Close();
DataSet ds = new DataSet("chart");
ad.Fill(ds,"set");
XmlDataDocument doc = new XmlDataDocument(ds);
doc.Save(Server.MapPath(Request.ApplicationPath)+"\\t.xml");
above is my code.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:18:22

Use this command string instead.

SELECT	name AS [@label],
	salary AS [@value]
FROM	Employee
FOR XML	PATH('set'),
	ROOT('chart')

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:24:30

[code]DECLARE @Employee TABLE
(
name VARCHAR(20),
salary VARCHAR(20)
)

INSERT @Employee
SELECT 'abcd', 'efgh' UNION ALL
SELECT 'ijkl', 'mnop'

-- Original
SELECT name AS [label],
salary AS [value]
FROM @Employee
FOR XML PATH('set'),
ROOT('chart')

-- Peso
SELECT name AS [@label],
salary AS [@value]
FROM @Employee
FOR XML PATH('set'),
ROOT('chart')[/code]

E 12°55'05.63"
N 56°04'39.26"

aneeshmm
Starting Member

20 Posts

Posted - 2009-01-29 : 06:27:07

thnx buddy...that works well except for it writes < instead of '<' and $gt; for '>' in the xml file and also gives '<XML_F52E2B61-18A1-11d1-B105-00805F49916B>' after the <set> tag.

aneeshmm
Starting Member

20 Posts

Posted - 2009-01-29 : 06:33:03

& l t ; instead of < and & g t ; instead of >

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-29 : 06:34:49

quote:

Originally posted by Peso

DECLARE	@Employee TABLE
	(
		name VARCHAR(20),
		salary VARCHAR(20)
	)

INSERT	@Employee
SELECT	'abcd', 'efgh' UNION ALL
SELECT	'ijkl', 'mnop'

-- Original
SELECT	name AS [label],
	salary AS [value]
FROM	@Employee
FOR XML	PATH('set'),
	ROOT('chart')

-- Peso
SELECT	name AS [@label],
	salary AS [@value]
FROM	@Employee
FOR XML	PATH('set'),
	ROOT('chart')

E 12°55'05.63"
N 56°04'39.26"

Impressive, gonna show my ignorance of XML here. Why does the @ in the aliases make that much of a difference?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:35:55

It turns the column value into an attribute instead of an element.
Copy the code and run it on a SQL Server 2005 or later machine.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:36:54

quote:
Originally posted by aneeshmm

thnx buddy...that works well except for it writes < instead of '<' and $gt; for '>' in the xml file and also gives '<XML_F52E2B61-18A1-11d1-B105-00805F49916B>' after the <set> tag.

You do not need the dataset transformation here.
All transformation is done by the query.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:39:07

Original output

<chart>
  <set>
    <label>abcd</label>
    <value>efgh</value>
  </set>
  <set>
    <label>ijkl</label>
    <value>mnop</value>
  </set>
</chart>

and the new output

<chart>
  <set label="abcd" value="efgh" />
  <set label="ijkl" value="mnop" />
</chart>

E 12°55'05.63"
N 56°04'39.26"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-29 : 06:47:13

quote:
Originally posted by Peso

It turns the column value into an attribute instead of an element.
Copy the code and run it on a SQL Server 2005 or later machine.

E 12°55'05.63"
N 56°04'39.26"

Thanks, yeah, I ran it in 2008.

You learn something new everyday..

aneeshmm
Starting Member

20 Posts

Posted - 2009-01-29 : 06:48:51

i got that. but when i create the xml file in my web form using the following code
'''' DataSet ds = new DataSet("chart");
ad.Fill(ds,"set");
XmlDataDocument doc = new XmlDataDocument(ds);
doc.Save(Server.MapPath(Request.ApplicationPath)+"\\t.xml"); '''
, if i dont provide the dataset name as chart and datatable name as set, it will take default as NewdataSet and Table. Is there any way to avoid this thing...the solution u gave is absolutely correct...but can i remove the first chart and set tags. The xml file now i get is like
<chart>
<set>
<xml>
<chart>
<set label=""abcd value="abcd" />
</chart>
</xml>
</set>
</chart>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:53:15

I am not knowledgeble in .Net programming language, but I believe you would want to do things this way instead.

SqlConnection cnn = new SqlConnection("data source=.\\sqlexpress; integrated security=true ;initial catalog=test");
cnn.Open();
SqlCommand c = new SqlCommand("SELECT name AS [@label], salary AS [@value] FROM Employee FOR XML PATH('set'), ROOT('chart')", cnn);
cnn.Close();

Set strData = c.Fields(0).Value

XmlDataDocument doc = new XmlDataDocument);
doc.xml = strDate
doc.Save(Server.MapPath(Request.ApplicationPath)+"\\t.xml");

E 12°55'05.63"
N 56°04'39.26"

aneeshmm
Starting Member

20 Posts

Posted - 2009-01-29 : 07:01:06

dataset cannot be set value like that. nyways thanks a lot dude. ur solution was perfect...i'll look for any programming tips to blend it to my requirement. i think i can use an xslt file to change the xml file. thnx again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources