Re: Dynamic attributes based on column values

Tech-Archive recommends: Fix windows errors by optimizing your registry

Not efficient, but it works (or at least I think it solves your problem).

Also, for the record, you cannot contruct the name of attributes dynamically
using XQuery.

CREATE FUNCTION UDF_GetEntityAddressesAsXML (@EntityID int)
RETURNS XML
AS
BEGIN
DECLARE @x xml

;WITH X1 AS
(SELECT DISTINCT
EntityID,
'<Address EntityID="' + CAST(P.EntityID as varchar(10)) + '" ' +
o.list + ' />' as Addr
FROM Personal AS p
CROSS APPLY
(SELECT ColumnName + '="' + [Value] + '" ' AS [text()]
FROM Personal
WHERE EntityID = p.EntityID
ORDER BY EntityID
FOR XML PATH('') ) o(list)
WHERE p.EntityID = @EntityID)
SELECT @x = CAST(Addr as xml)
FROM X1

RETURN @x
END

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--

"Brian" <pnkfloydlunatic@xxxxxxxxx> wrote in message
news:1176238623.872541.116790@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have been banging my head against the wall for TWO days. I have
gone back and forth with a very patient guy on thescripts.com. You
can see the ridiculous thread here

http://www.thescripts.com/forum/threadnav628777-1-10.html

If you have time, at least peruse that so we don't go in circles.
Anyway, if you guys can help me solve this, I will be forever
grateful!!

Here is the "basic" problem:

Here is an example for TWO different entities in the database.

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 and I don't know in advance what
the possible values will be? I also don't know how many attributes
there will be, but they can be different per entity, depending on how
they have set up an address in our application.

Another thing to note, is that I kind of have this working in an sproc
using PIVOT and generating a table with the values that have the
correct dynamic column names (you can see this on my other thread I
posted above) but I REALLY need this to not use dynamic SQL (so can
use it in a function) if possible and be able to be used in a select
statement, whether it be a temp table as I would like to get a result
set back that I can do a FOR XML RAW on. If this is confusing, it is
because I am delerious. OR is there a way to return a table from an
SPROC that has dynamic columns built?

Please help!! Thanks so much!!!

Brian



.