FOR XML PATH and dynamic attribute names

[pnkfloyd]

Hey,

I have a question regarding XML FOR PATH and SQL Server 2005. I have been using it successfully but I have run in to an issue I was hoping you could help with.

I have a couple of tables and I want to do this:

select  			
		StringValue as '@' + XmlFieldName, 
from defaultaddressfielddefs daf
inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
for xml path('Address'), type

so, I KNOW this will cause an SQL error, I am just trying to show you the concept. Basically, I want to be able to change the attribute name from "StringValu e" to a field in my row called XmlFieldName and have that attribute set to the value that is contained in the StringValue column.

How can this be done within a select statement?

Thanks!
Brian

[iburyak]

Try this:


[PHP]select 1 TAG,
null Parent,
XmlFieldName [Default!1!FileN ame]
from defaultaddressf ielddefs daf
inner join addressfields af on af.AddressField DefId = daf.AddressFiel dDefId
FOR XML EXPLICIT[/PHP]

Good Luck.

[pnkfloyd]

I am not sure how that would work. That syntax requires me to specify the attribute name in the [Default!1!FileN ame] syntax. I need the place where you have 'FileName' to have a dynamic name based on what is in the XmlFieldName column.

Try this:


[PHP]select 1 TAG,
null Parent,
XmlFieldName [Default!1!FileN ame]
from defaultaddressf ielddefs daf
inner join addressfields af on af.AddressField DefId = daf.AddressFiel dDefId
FOR XML EXPLICIT[/PHP]

Good Luck.

[iburyak]

Did you try this examle?

In line below

[PHP]XmlFieldName [Default!1!FileN ame]

XmlFieldName - is a column name from your table.
Default - can be changed it is a tag name I made up.
FileName - can be changed it is an atribute name I made up.[/PHP]

[pnkfloyd]

I am fairly familiar with the XML EXPLICIT syntax. Unless we are on a different page completely, I don't know how this will work for what I need. Here is an example of what I want to do.

Here is my TABLE

ID XmlFieldName StringValue
1 Pepsi Free
2 Coke Zero
...

I want to make the resulting XML be, for every row

<ROOT>
<Default Pepsi="Free">
<Default Coke="Zero">
</ROOT>

It isn't a matter of changing the attribute name once, as in your example and using that for every attribute. It is a matter of changing the attribute to be the actual value in the XmlFieldName column and making that attribute value be "SomeString " (the value in my StringValue column)

Thanks for your help, I hope there is a solution to this.

Did you try this examle?

In line below

[PHP]XmlFieldName [Default!1!FileN ame]

XmlFieldName - is a column name from your table.
Default - can be changed it is a tag name I made up.
FileName - can be changed it is an atribute name I made up.[/PHP]

[iburyak]

I never saw XML not talking of a query where attribute is a variable.
Well formed XML has static attributes.

The only way I know how to do is

[PHP]<ROOT>
<Default name="Pepsi" value="Free">
<Default name="Coke" vlue="Zero">
</ROOT>[/PHP]


Let me know if you are interested.
Thank you