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 "StringValue" 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!FileName]
from defaultaddressfielddefs daf
inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
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!FileName] 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!FileName]
from defaultaddressfielddefs daf
inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
FOR XML EXPLICIT[/PHP]

Good Luck.

[iburyak]

Did you try this examle?

In line below

[PHP]XmlFieldName [Default!1!FileName]

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!FileName]

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