current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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..

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
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()... –  Amy 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.