Need a select command for getting table column values into an xml attribute (value) for a another column value as an attribute name.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi All

Please help out with an sql command that gives xml as an resultant(output). Below example give you a brief info about my requirement.

CustNo Pname Pid
1 Appel AP
1 Orange OR
1 Mango MG
2 Appel AP
2 Orange OR
3 Appel AP

that means, i have three columns. Now i need an Below xml from Sql select command.

XML

<Sample>
<custNo  ID="1">
<seq Appel= "AP" Orange="OR" MANGO="MG"/>
</custNo>
<custNo  ID="2">
<seq Appel= "AP" Orange="OR" />
</custNo>
<custNo  ID="3">
<seq Appel= "AP"/>
</custNo>
</Sample>

Thanks & Regardes
Chiranjeevi

Posted 9-May-13 22:37pm

ommi.chiru

Add a Solution

Comments

CHill60 10-May-13 4:40am

What have you tried so far?

Zoltán Zörgő 10-May-13 5:48am

I am sure this can be done in t-sql but do you really need to do this in sql? It is far more easier in an above layer where you have a high level language - this is even more valid if the list of fruits is not limited. I can imagine what you want to achieve, but you could achieve this in some other form, like storing the attributes of a customer in an xml field, and not every property in a record.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Fabricio Dos Santos Antunes · Accepted Answer · 2013-05-10T09:52:00

Solution 3

SQL

select
	custNo.HUEid
	,( 
		select [Appel],[Orange],[Mango] from 
		(select *  from hue h where HUEid = custNo.HUEid) p
		Pivot(
		max(pid) for HUENome in ( [Appel],[Orange],[Mango])
		) as seq
		For xml auto  ,type 
	)
 from hue custNo
 group by hueid 
For xml auto ,root ('Sample')

This is my T-Sql version using Pivot and For Xml, hope this help =)

Posted 10-May-13 9:52am

Fabricio Dos Santos Antunes

Comments

RedDk 10-May-13 16:29pm

Wow, certainly insightful ... going to be thinking about this style for a while. Thanks!

Fabricio Dos Santos Antunes 10-May-13 20:28pm

Thank you, Pivot is very usefull in many ways

datt265 · Accepted Answer · 2013-05-10T08:33:00

VB

// Start XML file, echo parent node
echo '<sample>';

// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE

  echo 'custNo="' . parseToXML($row['ID']) . '" ';
  echo 'seq="' . $row['AP'] . '" ';
  echo '/>';
}

// End XML file
echo '</sample>';