XQuery - Only return if not null

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi there

I have the following (simplyfied) xml document, which is hold in one of my table columns:

<Root>
<ElementABC P1="07" P2="07"/>
<ElementDEF P1="09" P2="07" P4="43" />
<ElementGHI P1="01" P2="07" P8="43" />
</Root>

Now I want to transpose the value of the P1 parameter into a new column named ColumnA. This works fine with the following statement:

SELECT HhID, MetUNo, StartDate,
AttributeDataXml.value('(Root/ElementABC/@P1)[1]','varchar(200)') as ColumnA,
FROM tTestTable

The problem is that my XML document does sometimes contatin the ElementABC and sometimes the ElementDEF. So if the ElelmetABC is found, then i want to use it's P1 value for the ColumnA. If it is not there the i want to try if the ElementDEF is found and use it's value for ColumnA.

I need some kind if if then else or how do I get what I want? I tried:
SELECT HhID, MetUNo, StartDate,
AttributeDataXml.value('(Root/ElementABC/@P1 or Root/ElementDEF/@P1)[1]','varchar(200)') as ColumnA,
FROM tTestTable

But this does then result in the value of true/false :-)

Is there anyone how knows how to solve this? Any help would really be appreciated!
Thanks a lot!


.

Relevant Pages

  • Re: transpose
    ... Try this to use TRANSPOSE() .. ... because you did not array-enter the formula ... i have numbers running down columnA and letters running across ...
    (microsoft.public.excel)
  • Re: transpose
    ... Try copying the whole range and then using Paste / Special / Transpose. ... i have numbers running down columnA and letters running across ...
    (microsoft.public.excel)
  • Re: XQuery - Only return if not null
    ... If it is not there the i want to try if the ElementDEF is found and use it's value for ColumnA. ... that way if ElementABC/@P1 exists, it is the first item in the sequence, if not, then ElementDEF/@P1 is the first item. ... Martin Honnen --- MVP XML ...
    (microsoft.public.sqlserver.xml)
  • Re: XQuery - Only return if not null
    ... If it is not there the i want to try if the ElementDEF is found and use it's value for ColumnA. ... that way if ElementABC/@P1 exists, it is the first item in the sequence, if not, then ElementDEF/@P1 is the first item. ... Martin Honnen --- MVP XML ...
    (microsoft.public.sqlserver.xml)