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