XML tag formation using sql query

Question

My Xml Looks like this:

biometrictDate, biometricID,dateOfBirth, firstName, gender, 
lastName, consumerUserId, MedicalHeightValue

are all columns from a table.

<Assessment biometrictDate="20120305 08:03:00" biometricID="74330759" 
            dateOfBirth="1975-04-08" firstName="BRYAN" gender="M" lastName="HAYES" 
            consumerUserId="120004223500"> 
    <HealthAttribute>
        <Identifier>MedicalHeightValue</Identifier>
        <Value>67</Value>
    </HealthAttribute>
</Assessment>

MedicalHeightValue should alone be placed in between HealthAttribute tags which is completed using the following query:

select C.Value, C.Identifier
from TableA
    outer apply (values
        ('MedicalHeightValue', MedicalHeightValue) ) as C(Identifier, Value)
for xml path('HealthAttribute')

Now I want the following columns alone in Assessment tag

{biometrictDate, biometricID, dateOfBirth, firstName, gender, lastName, consumerUserId}

Any help please?

New XML should look like this:

<Assessment biometrictDate="20120305 08:03:00" biometricID="74330759" 
            dateOfBirth="1975-04-08" firstName="BRYAN" gender="M" lastName="HAYES" 
            consumerUserId="120004223500"> 
   <HealthAttribute> 
      <Identifier>MedicalHeightValue</Identifier> 
      <Value>67</Value> 
   </HealthAttribute> 
</Assessment>

Can you **show us** what your new XML should look like? Not very clear from your description.... — marc_s, Oct 22 '13 at 05:46
Please **do not** put code samples or sample data into comments - since you cannot format it, it's **extremely hard** to read it.... Instead: **update** your question by editing it to provide that additional information! Thank you. — marc_s, Oct 22 '13 at 05:49
ok m sry.I am not able to edit my original post.Is the question clear or do you need any additional info ?Please let me know — Poornima, Oct 22 '13 at 05:59
I don't see any difference between the two sets of XML you're showing..... — marc_s, Oct 22 '13 at 06:01
Hi marc_s,My output should XML should look like the ones i have shown{Both are same}.I need to write a sql query to get the output in the format as specified above. — Poornima, Oct 22 '13 at 06:09

score 0 · Answer 1 · edited May 23 '17 at 11:50

First of all, it's REALLY hard to understand what do you want to get. I think I've figured this out from your question and your pervious question (which, BTW, still don't have an accepted answer).

Here's query you need:

select
    A.biometrictDate, A.biometricID, A.dateOfBirth,
    A.firstName, A.gender, A.lastName, A.consumerUserId,
    (
        select *
        from (values
            ('MedicalHeightValue', A.MedicalHeightValue),
            ('MedicalWeightValue', A.MedicalWeightValue)
        ) as V(Identifier, Value)
        for xml path('HealthAttribute'), type
    )
from table1 as A
for xml raw('Assessment')

you can also do it like this, to have more control over names:

select
    A.biometrictDate as [@biometrictDate],
    A.biometricID as [@biometricID],
    A.dateOfBirth as [@dateOfBirth],
    A.firstName as [@firstName],
    A.gender as [@gender],
    A.lastName as [@lastName],
    A.consumerUserId as [@consumerUserId],
    (
        select *
        from (values
            ('MedicalHeightValue', A.MedicalHeightValue),
            ('MedicalWeightValue', A.MedicalWeightValue)
        ) as V(Identifier, Value)
        for xml path('HealthAttribute'), type
    )
from table1 as A
for xml path('Assessment')

sql fiddle demo

XML tag formation using sql query

1 Answers1