Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Help using .exist() to test for XML element existance

1 view
Skip to first unread message

Farmer

unread,
Sep 24, 2009, 9:16:10 AM9/24/09
to

Please help me to filter out only CustomerRet elements that DO NOT have child ParentRef element usign .exist().

 

declare @xml xml

SET @xml =

'<CustomerRet>

        <ListID>160000-933272658</ListID>

        <TimeCreated>1999-07-29T20:24:18+03:00</TimeCreated>

        <TimeModified>2011-12-16T02:24:51+01:00</TimeModified>

        <EditSequence>1323998691</EditSequence>

        <IsActive>true</IsActive>

        <ParentRef>

          <ListID>150000-933272658</ListID>

          <FullName>Abercrombie, Kristy</FullName>

        </ParentRef>

</CustomerRet>

<CustomerRet>

        <ListID>160000-93</ListID>

        <TimeCreated>1999-07-29T20:24:18+03:00</TimeCreated>

        <TimeModified>2011-12-16T02:24:51+01:00</TimeModified>

        <EditSequence>1323998691</EditSequence>

        <IsActive>true</IsActive>

</CustomerRet>'

-- correct result

SELECT

    t.c.value('ListID[1]', 'nvarchar(50)')         as [ciInfo3]

FROM @xml.nodes('/CustomerRet') as t(c)

OUTER APPLY t.c.nodes('ParentRef') as cus(c)

WHERE cus.c.value('ListID[1]', 'varchar(50)') IS NULL

--WHERE @xml.exist('ParentRef') = 0

 

-- Help make these work

SELECT

    t.c.value('ListID[1]', 'nvarchar(50)')         as [ciInfo3]

FROM @xml.nodes('/CustomerRet') as t(c)

WHERE @xml.exist('./ParentRef') = 0

 

SELECT

    t.c.value('ListID[1]', 'nvarchar(50)')         as [ciInfo3]

FROM @xml.nodes('/CustomerRet') as t(c)

WHERE @xml.exist('not(./ParentRef)') = 1

 

Farmer

unread,
Sep 24, 2009, 9:20:44 AM9/24/09
to
I GOT IT! THANK YOU!
 

SELECT

t

.c.value('ListID[1]', 'nvarchar(50)') as [ciInfo3]

FROM

 @xml.nodes('/CustomerRet') as t(c)

WHERE

t.c.exist('./ParentRef') = 0

Bob

unread,
Sep 24, 2009, 2:39:01 PM9/24/09
to
You can also do that like this:

SELECT t.c.value('ListID[1]', 'nvarchar(50)') as [ciInfo3]

FROM @xml.nodes('/CustomerRet[not (ParentRef)]') as t(c)

"Farmer" wrote:

> I GOT IT! THANK YOU!
>
> SELECT
>

> t.c.value('ListID[1]', 'nvarchar(50)') as [ciInfo3]
>
> FROM @xml.nodes('/CustomerRet') as t(c)
>

> WHERE t.c.exist('./ParentRef') = 0

Farmer

unread,
Sep 24, 2009, 7:13:41 PM9/24/09
to
Thank you, Bob. That is cool way.


"Bob" <B...@discussions.microsoft.com> wrote in message
news:CE3F78B6-A8E6-48E8...@microsoft.com...

0 new messages