How do i add attributes in sql for xml

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Viknes
    New Member
    • Jun 2014
    • 1

    How do i add attributes in sql for xml

    (select
    '99999' as [ram:ID],
    /* the rest of the codes */


    out put :
    <ram:ID> 99999 </ram:ID>

    but what i need is :

    <ram:ID schemeAgencyID= "1"> 99999 </ram:ID>
  • Luuk
    Recognized Expert Top Contributor
    • Mar 2012
    • 1043

    #2
    after looking at this
    something like:

    Code:
    select 1 as Tag, null as Parent, 
        FromDate as [PHD!1!test],
    	null as [FT!2!FromDate!ELEMENT], 
    	null as [FT!2!ToDate!ELEMENT]
    from PHD
    union all
    select 2 as Tag, 1 as Parent, '',
    	FromDate, 
    	ToDate
    from PHD
     for xml EXPLICIT
    results in:
    Code:
    <PHD test="2014-04-15T10:00:00" />
    <PHD test="2014-04-25T10:00:00" />
    <PHD test="2014-05-16T10:00:00" />
    <PHD test="2014-05-18T10:00:00">
      <FT>
        <FromDate>2014-04-15T10:00:00</FromDate>
        <ToDate>2014-06-01T12:00:00</ToDate>
      </FT>
      <FT>
        <FromDate>2014-04-25T10:00:00</FromDate>
        <ToDate>2014-05-21T12:00:00</ToDate>
      </FT>
      <FT>
        <FromDate>2014-05-16T10:00:00</FromDate>
        <ToDate>2014-05-16T12:00:00</ToDate>
      </FT>
      <FT>
        <FromDate>2014-05-18T10:00:00</FromDate>
        <ToDate>2014-05-31T18:00:00</ToDate>
      </FT>
    </PHD>
    i dont know how to get rid of the extra 'empty' PDH tags, but this example could be helpfull (in combination with the link)

    Comment

    Working...