Dynamic Attributes using XQuery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Dynamic Attributes using XQuery?

Author

Topic

pnkfloydloon
Starting Member

4 Posts

Posted - 2007-04-09 : 11:17:13

Can someone please help me do something like this, below. This will not work as it doesn't like the dynamic attribute name (the {sql:column("daf.XmlFieldName")} part). There has to be some way for SQL Server 2005 to generate XML with an attribute based on a value in a column. I know this may not be the best way to do it, but I have to generate XML that validates against a schema that gets generates with X attributes that are based on valid DB columns. So I need my attributes to be able to be named accordingly.

Please help!!

Thanks so much
Brian


DECLARE @myXML xml
SET @myXML = ''

SELECT top 1 daf.XmlFieldName, af.StringValue, @myXML.query('   			
		<Address
			"{sql:column("daf.XmlFieldName")}"="{sql:column("af.StringValue")}"			
		/>')
	AS Result
from defaultaddressfielddefs daf
inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
where af.StringValue <> ''

pnkfloydloon
Starting Member

4 Posts

Posted - 2007-04-09 : 16:26:38

Anyone? I am desperate!

thanks!

brian

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-09 : 18:23:00

I'm not sure how your output should look like. Can you just build a xml string and cast it to xml as shown below?

SELECT cast('<Address ' + daf.XmlFieldName + '=' + af.StringValue + '/>' as xml)
AS Result
from defaultaddressfielddefs daf
inner join addressfields af on af.AddressFieldDefId = daf.AddressFieldDefId
where af.StringValue <> ''

BTW, is there any reason why you are using xquery? Is this achievable using 'For XML' clause?

pnkfloydloon
Starting Member

4 Posts

Posted - 2007-04-09 : 20:06:06

Here is an example for TWO different entities

EntityID XmlFieldName Value
1 City Austin
1 State TX
1 Country US
2 CityName Los Angeles
2 StateCode CA
2 CountryCode US
2 Zip 111111

Here is how the two different results should be

where EntityID = 1
<Address City="Austin" State="TX" Country="US"/>

where EntityID = 2
<Address CityName="Los Angeles" StateCode="TX" CountryCode="US" Zip="111111"/>

Notice how the attribute names (City or CityName, State or StateCode, etc) are based off the XmlFieldName actual value and I don't know in advance what the possible values will be?

Thanks so much!!!

Brian

pnkfloydloon
Starting Member

4 Posts

Posted - 2007-04-10 : 12:17:45

Still very desperate

This doesn't seem hard but I have gone down every path known to man (except of course the one that works)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources