How to modify values while extracting them from XML data in SQL Server

Question

Here is a question on XQuerying in SQL (SQL Server 2008)

Originally, I have an XML that I need to need to output somewhat differently, for reference:

declare @XMLNODE table (id int identity, doc xml)
insert @XMLNODE (doc) values (   
'
 <Root>
    <Elements>
      <Items>
        <OldItem>

          <ID>1</ID>
          <Show Pointer="yes" />
          <Display Pointer="Display">
            <Detail1>some Details</Detail1>
          </DisplayDetails>

        </OldItem>
      </Items>
    </Elements>
    </Root>'
)

    SELECT  a.value('(ID)[1]','int') as ID,
    a.value('(Show/@Pointer)[1]', 'varchar(5)') AS ShowItem,
    a.value('Display[1]/@Pointer[1]="Display"', 'varchar(10)') as DisplayDetails, -- Set to 'true' or 'false'
    a.value('DisplayDetails[1][@Pointer[1]="Display"]/Detail1[1]', 'varchar(max)') as Detail1
    FROM    @XMLNODE t
    cross apply
    t.doc.nodes('//OldItem') x(a)
    FOR XML PATH ('Items'),
    ROOT('Elements')

So, now I have an XML like this:

<Elements>
  <Items>
    <ID>1</ID>
    <ShowItem>yes</ShowItem>
    <DisplayDetails>true</DisplayDetails>
    <Detail1>some Details</Detail1>
  </Items>
</Elements>

Want to add some modify this to 1 or no value, based on if it was true/yes etc.

i.e. desired:

<Elements>
  <Items>
    <ID>1</ID>
    <ShowItem>1</ShowItem>
    <DisplayDetails>1</DisplayDetails>
    <Detail1>some Details</Detail1>
  </Items>
</Elements>

Also, if <DisplayDetails>false</DisplayDetails> I just want to update it to <DisplayDetails />.

Is there a way to directly compare the inputs in the query (for example, yes or no) and set it to 1 or 0 instead of true or false? I am using a .query to use these values and add additional information as needed..

score 0 · Accepted Answer · answered Apr 20 '11 at 16:25

I don't query XML from SQL very much, but if I were doing this for anything that wasn't XML, I'd do something like this in the SELECT statement:

SELECT  a.value('(ID)[1]','int') as ID,
case 
  when a.value('(Show/@Pointer)[1]', 'varchar(5)') = 'yes' then '1'
  else '0'
End case as 'ShowItem',
a.value('Display[1]/@Pointer[1]="Display"', 'varchar(10)') as DisplayDetails, -- Set to 'true' or 'false'
a.value('DisplayDetails[1][@Pointer[1]="Display"]/Detail1[1]', 'varchar(max)') as Detail1
FROM    @XMLNODE t
cross apply
t.doc.nodes('//OldItem') x(a)

Note: this hasn't been tried, and I'm not on a DB client to make sure my syntax is exactly correct. However, checking a value from the DB and showing something else based on that value is relatively straight-forward.

thanks, I'll give it a try fixing it as needed, any idea if we can do such a comparison check inside a .query() while querying SQL XML data? since I can loop through the values, add more XML data etc. directly in that, only not sure how to 'modify' the XML inside a .query()... — Loser Coder, Apr 20 '11 at 16:29
Like I said, I don't query SQL XML very much, so I'm kind of at my limit with this. If you hit another problem, I'd just add comments to this question or ask a different question with the specific info you need. — AllenG, Apr 20 '11 at 16:38

How to modify values while extracting them from XML data in SQL Server

1 Answers1