SQL Server WHERE clause XPath with multiple nodes and conditions

Question

I have this XML in a table column in SQL Server:

<root>
    <Request>
        <RequestData>
            <VendorLeadList>
                <VendorLeadItem>
                    <CampaignOfferTypeID>REN</CampaignOfferTypeID>
                    <LeadDispositionID>Lead</LeadDispositionID>
                    <Jurisdiction>NY</Jurisdiction>
                    <FirstName>Shikasta</FirstName>
                    <LastName>Kashti</LastName>
                    <MessageId>1347_1483825159115_c8273</MessageId>
                </VendorLeadItem>
            </VendorLeadList>
        </RequestData>
        <DualMessageID/>
        <AzureBlobFile/>
        <AzureBlobImageList/>
    </Request>
</root>

I want to query all the records where it matches some nodes with specific values. For example I want all records where LeadDispositionID=Lead and Jurisdiction=NY and CampaignOfferTypeID=REN and a MessageId element exists (doesn't matter what value.)

I tried this but it doesn't work (no errors but the conditions doesn't match and it returns other records):

SELECT TOP 10 *
  FROM [Messages]
  WHERE PayLoadXml.exist('//LeadDispositionID[.="Lead"] and //CampaignOfferTypeID[.="REN"] and //Jurisdiction[.="NY"] and //MessageId') = 1
  ORDER BY ID DESC

Any idea as to what I'm doing wrong?

your xpath is definitely fine here. I'd recon there something with PayLoadXml.exist() function. — Granitosaurus
– Granitosaurus, Commented Jan 10, 2017 at 16:09
@Granitosaurus no, you cannot simply combine nodes with and... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 10, 2017 at 17:27
@Shnugo What do you mean? and is completely valid xpath and you can combine nodes like that, xpath will evaluate every node individually and return either '1' if there are matches, otherwise '0'. — Granitosaurus
– Granitosaurus, Commented Jan 10, 2017 at 18:12
@Granitosaurus Just use my sample code and place WHERE itm.exist(N'LeadDispositionID[.="Lead"] and Jurisdiction[.="NY"]')=1 as final line. Try it with and Jurisdiction[.="NY"] and without. Try it even with something like and Jurisdiction[.="Blah"] or and xyz[.="Blah"]. You won't get the expected result... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 10, 2017 at 22:43

Gottfried Lesigang · Accepted Answer · 2017-01-10 22:46:24Z

You cannot combine nodes within .exist() simply with and. Your own example would work like this:

SELECT TOP 10 *
FROM @Messages
WHERE PayLoadXml.exist('//VendorLeadItem[LeadDispositionID[.="Lead"] and CampaignOfferTypeID[.="REN"] and Jurisdiction[.="NY"] and MessageId/text()]') = 1

Try it like this:

First a declared table to mock-up your Messages table. Insert 3 cases:

DECLARE @messages TABLE(SomeDescription VARCHAR(100),PayLoadXml XML);
INSERT INTO @messages VALUES
('Your example'
,'<root>
    <Request>
        <RequestData>
            <VendorLeadList>
                <VendorLeadItem>
                    <CampaignOfferTypeID>REN</CampaignOfferTypeID>
                    <LeadDispositionID>Lead</LeadDispositionID>
                    <Jurisdiction>NY</Jurisdiction>
                    <FirstName>Shikasta</FirstName>
                    <LastName>Kashti</LastName>
                    <MessageId>1347_1483825159115_c8273</MessageId>
                </VendorLeadItem>
            </VendorLeadList>
        </RequestData>
        <DualMessageID/>
        <AzureBlobFile/>
        <AzureBlobImageList/>
    </Request>
</root>'
)
,('LeadDispositionID=Slave'
,'<root>
    <Request>
        <RequestData>
            <VendorLeadList>
                <VendorLeadItem>
                    <CampaignOfferTypeID>REN</CampaignOfferTypeID>
                    <LeadDispositionID>Slave</LeadDispositionID>
                    <Jurisdiction>NY</Jurisdiction>
                    <FirstName>Bruno</FirstName>
                    <LastName>Kashti</LastName>
                    <MessageId>1347_1483825159115_c8273</MessageId>
                </VendorLeadItem>
            </VendorLeadList>
        </RequestData>
        <DualMessageID/>
        <AzureBlobFile/>
        <AzureBlobImageList/>
    </Request>
</root>'
)
,('LeadDispositionID=Lead but No MessageId'
,'<root>
    <Request>
        <RequestData>
            <VendorLeadList>
                <VendorLeadItem>
                    <CampaignOfferTypeID>REN</CampaignOfferTypeID>
                    <LeadDispositionID>Lead</LeadDispositionID>
                    <Jurisdiction>NY</Jurisdiction>
                    <FirstName>Bruno</FirstName>
                    <LastName>Kashti</LastName>
                    <MessageId></MessageId>
                </VendorLeadItem>
            </VendorLeadList>
        </RequestData>
        <DualMessageID/>
        <AzureBlobFile/>
        <AzureBlobImageList/>
    </Request>
</root>'
);

This is the query: The CROSS APPLY will ensure, that only nodes with a MessageId are taken into account. The WHERE will apply an additional filter

SELECT m.*
FROM @messages AS m
CROSS APPLY m.PayLoadXml.nodes(N'/root/Request/RequestData/VendorLeadList/VendorLeadItem[not(empty(MessageId/text()))]') AS A(itm)
WHERE itm.exist(N'LeadDispositionID[text()="Lead"]')=1

If you need to check more than one condition you might use this:

WHERE itm.exist(N'.[LeadDispositionID="Slave" and FirstName="Bruno"]')=1

Collectives™ on Stack Overflow

SQL Server WHERE clause XPath with multiple nodes and conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related