Post reply

for xml path query, nested nodes, do not include html translation

  • November 23, 2011 at 10:46 am

    #99935

    I have the following query:

    select

    r.ReviewUID as "id",

    r.RTitle as "title",

    r.RDescription as "description",

    r.CreatedDateUTC as "createdDate",

    r.ImplementationDate as "implementationDate",

    r.EffectiveDateUTC as "effectiveDate",

    r.StateID as "state/id",

    st.ColumnInt as "state/columnInt",

    st.ColumnValue as "state/columnValue",

    (select n.AttachType as "attachType/id", at.ColumnInt as "attachType/columnInt", at.ColumnValue as "attachType/columnValue",

    n.NoteText as "text" from rcs.tblNotes n left join rcs.tblLookup at on n.AttachType = at.LookupID

    where n.EntityUID = r.ReviewUID FOR XML Path('note')) as "notes"

    from rcs.tblReview r

    left join rcs.tblLookup st on r.StateID = st.LookupID

    where r.ReviewUID = '4412567C-7EFD-41E1-BAC2-747790C99018'

    FOR XML Path('review')

    which returns almost exactly what I need:<review>

    <id>4412567C-7EFD-41E1-BAC2-747790C99018</id>

    <title>FancyNewName</title>

    <description>A lot to say about this test review</description>

    <createdDate>2011-11-23T12:06:17</createdDate>

    <implementationDate>2011-11-27T12:06:17</implementationDate>

    <effectiveDate>2011-11-27T12:06:17</effectiveDate>

    <state>

    <id>17</id>

    <columnInt>1</columnInt>

    <columnValue>Edit</columnValue>

    </state>

    <notes>& lt;note>& lt;text& gt;An addition note to add to the review& lt;/text& gt;& lt;/note& gt;</notes>

    </review>

    Only I do not want the "<" and ">" characters to be escaped for html. I want them raw. How do I alter what I have above to get exactly this only really as just xml?

    PLEASE NOTE: The spaces in the html escape characters were added so this would show up correctly in this post. Otherwise, the parsers were showing what was being escaped. The spaces do not actually exist in my output.

    Thanks!

  • November 23, 2011 at 11:26 am

    #1412385

    I found it. I needed to add TYPE to the end of my XML Path statement.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply