Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    4

    Unanswered: XML with dynamic attributes based on column values

    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/thre...8777-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

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i am not sure if i could understand the requirement properly. however it seems that u want a routine, preferably a function, that can return a varchar (containing the XML) for an EntityId passed to the function/proc.

    if the above is true try this,
    Code:
    create function Func1 (@EntId int)
    returns varchar(1000) 
    as
    begin
       declare @Retu as varchar(1000)
       set @Retu = ''
       select @Retu = @Retu + XmlFieldName  + '="' + Value + '" ' from   <someTable> where EntityID=2
       return '<Address ' + @Retu + ' />'
    end

  3. #3
    Join Date
    Apr 2007
    Posts
    4
    Close, but I have to be able to use this within another select statement that uses FOR XML PATH. The code below gives me EVERY entity and it correctly puts the XML in the right place, associated with the corresponding entity.

    An entity can have multiple address types (work, home, etc), so for a given entity, I can get back multiple <Address> elements. Your example is very close, but fails when that entity has multiple address types, and in that case, all the attributes are added to a single Address element and I need to be able to do it like I do the phone and email below.

    The reason I need to use this within a select statement is I have something like this below. Notice the red part, that is where I am having trouble getting back the right XML for that particular entity. In the other subselects, the XML comes back properly and is "injected" in to the right place because of the order by EntityID on each query.

    Since the address attributes are "dynamic" and can vary per entity, how would I do what I want to do below to fit in with this query? I hope that explains better?

    It does seem your query is CLOSE, I could easily do a "select xmlResult from someFunc(mainInd.EntityID)" but how do I make it give me back all the address types like

    <Addresses>
    <Address AddressType="Work" Street1="blah" etc/>
    <Address AddressType="Home" StreetName="blah" Country="US">
    </Addresses>


    Thanks SO much
    Brian


    Code:
    -- membership section of individual
    select e.MemberId as '@EntityReferenceNumber',
    
    -- this select the individuals phone information
    (select 
    	(select Description from CustomPhoneTypes where PhoneTypeId = pn.PhoneTypeId) as '@PhoneType',
    	CountryCode as '@CountryCode',
    	AreaCode as '@AreaCode',		
    	Number as '@Number',
    	Extension as '@Extension',	
    	from PhoneNumbers pn
    		where pn.EntityId = mainInd.IndividualId		
    	order by pn.EntityId 
    	FOR XML PATH('PhoneNumber'), ROOT('PhoneNumbers'), TYPE
    ),
    
    -- this select the individuals email address information
    (select 
    	(select Description from CustomEmailTypes where EmailTypeId = ea.EmailTypeId) as '@EmailType',
    	Email as '@EmailAddress',	
    	from EmailAddresses ea
    		where ea.EntityId = mainInd.IndividualId		
    	order by ea.EntityId 
    	FOR XML PATH('EmailAddress'), ROOT('EmailAddresses'), TYPE
    ),
    
    select here to get the address information how???
    
    
    FROM Individuals mainInd inner join
    	Entities e on e.EntityId = mainInd.IndividualId inner join 
    	Membership m on m.EntityId = e.EntityId AND m.AssociationID = 2499
    FOR XML PATH('Individual'), ROOT('Individuals')

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i am not sure why u need to use FOR XML PATH and what the big sql povided by u does - as tables etc needed to run the sql was not supplied.

    once again, what i understand is.... u have another field, that u have not mentioned earlier, EntityType which store the type of address. and u want each <Addresses> element to include all the Types for that entity as separate child xml elements.

    so we have the new table as
    Code:
    create table SomeTable (
    	EntityID int,
    	EntityType varchar(20),
    	XmlFieldName varchar(50),
    	Value varchar(50) 
    )
    and sample data like
    Code:
    EntityID  EntityType  XmlFieldName     Value            
    --------- ----------- ---------------- ---------------- 
    1         Home        City             Austin
    1         Home        State            TX
    1         Home        StreetAddress1   222 some street
    1         Office      City             Austin
    1         Office      State            TX
    2         Home        StreetAddress1   333 some street
    2         Home        ZipCode          666666
    now all that we need to produce a result is a wrapper function and little modification of the function defined in my prvious post

    Code:
    create function Func1 (@EntId int, @EntType varchar(20))
    returns varchar(1000) 
    as
    begin
       declare @Retu as varchar(1000)
       set @Retu = '<Address AddressType="' + @EntType + '" '
       select @Retu = @Retu + XmlFieldName  + '="' + Value + '" ' from   SomeTable where EntityID=@EntId and EntityType=@EntType
       return @Retu + ' />'
    end
    finally, for 1 entity, u can call the above as
    Code:
       declare @Retu as varchar(2000)
       set @Retu = '<Addresses>' + char(13)
       select @Retu = @Retu + dbo.Func1(1,aa.EntityType) + char(13) from  (select distinct EntityType from SomeTable where EntityID=1) as aa
       print  @Retu + '</Addresses>'
    and for all entity, u can call the above as
    Code:
       declare @Retu as varchar(2000)
       set @Retu = '<Addresses>' + char(13)
       select @Retu = @Retu + dbo.Func1(aa.EntityId,aa.EntityType) + char(13) from  (select distinct top 100 percent EntityId,EntityType from SomeTable order by EntityID) as aa
       print  @Retu + '</Addresses>'
    hope we are into another "ridiculous thread" like the one u had with thescripts.com

  5. #5
    Join Date
    Apr 2007
    Posts
    4
    nope, this one won't get ridiculous! that was the solution I finally came up with yesterday! well, a small variation of it. I wish I had found you sooner!!!

    Thanks SO much for the help!!

    Brian

  6. #6
    Join Date
    Apr 2007
    Posts
    4
    One more quick question, hopefully. When building XML that way, every once in a while, I get back something about invalid whitespace, or maybe an invalid character. What is a good way to handle that?

    Thanks
    brian

  7. #7
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    if u r dealing with non-english characters u might need to change the encoding of the xml. there could be many problems with xml, there isnt a generic solution .....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •