sql server 2008 - Query XML with dynamic number of elements or attributes? -

i have employee table in sql database has fixed attributes employee_id, employee_name , employee_age, allow user add n attributes of choice in ui. want write stored procedure store data user provides fixed attributes attributes has created.

the solution thought pass xml parameter stored procedure have following structure. if employee_age not provided, default value stored.

<employee>   <employee_id>1<employee_id>   <employee_name>xyz<employee_name>   <dynamic_attrib1>2.34</dynamic_attrib1>   <dynamic_attrib2>abcd</dynamic_attrib2> <employee> 

the problem facing how parse xml when dont know names of attributes user setting in advance? can way? there other way in can it? in advance.. :)

first of all: given xml not valid...

what trying pattern called key-value-pair (or attribute-value-pair or entity-attribute-value) , cumbersome... on database side better know structure of resultset, otherwise need (ugly) tricks.

you can transfer given xml classical key-value-pairs this:

declare @xml xml= '<employee>   <employee_id>1</employee_id>   <employee_name>xyz</employee_name>   <dynamic_attrib1>2.34</dynamic_attrib1>   <dynamic_attrib2>abcd</dynamic_attrib2> </employee>';  select nd.value('local-name(.)','varchar(max)') nodename       ,nd.value('.','varchar(max)') @xml.nodes('employee/*') a(nd); 

togehter helper table can change nodenames, if there corresponding entry:

declare @dynattribs table (attribname varchar(100),name varchar(100)); insert @dynattribs values  ('dynamic_attrib1','whatever can 2.34') ,('dynamic_attrib2','whatever can "abcd"');  select isnull(da.name, nd.value('local-name(.)','varchar(max)')) nodename       ,nd.value('.','varchar(max)') @xml.nodes('employee/*') a(nd) left join @dynattribs da on nd.value('local-name(.)','varchar(max)')=da.attribname; 

you might include datatype helper table, force use dynamically created statements , exec.

better xml this:

declare @xml xml= '<employee>   <employee_id>1</employee_id>   <employee_name>xyz</employee_name>   <dynamicattributes>       <attribute name="testname1">2.34</attribute>       <attribute name="testname2">abcd</attribute>   </dynamicattributes> </employee>';  select e.value('employee_id[1]','int') employee_id       ,e.value('employee_name[1]','varchar(max)') employee_name       ,da.value('@name','varchar(max)') nodename       ,da.value('.','varchar(max)') @xml.nodes('employee') a(e) outer apply e.nodes('dynamicattributes/*') b(da); 

but question not provide enough details: going this? structure (maybe later!) more complex (inner nested elements address, phone number(s))? better define sure set of attributes , allow customer define of them not used. maybe allow them give individual names (aliases). not advise offer open do-it-yourself database. deep troubles...