Alternate nodes in a SQL Server XQuery path?

user_80209 0
Viewed 42

Is there any way to have a sort of "alternates group" like in Regular Expressions, in an XQuery path in SQL Server?

I have this query...

SELECT Q.ROWID QUEUEID, Q.DOCUMENTPACKAGETYPE, 
    B.R.value('@_ID', 'NAME') PARTYID,
    B.R.value('@_BorrowerID', 'NAME') BORROWERID,
    B.R.value('@_Name', 'NAME') NAME,
    B.R.value('@_EmailAddress', 'NAME') EMAILADDRESS
FROM docutech.QUEUE_EX Q 
CROSS APPLY Q.DATA.nodes('LOAN_PUSHBACK_PACKAGE/EVENT_DATA/ESIGN/PARTY') AS B(R)
WHERE Q.REASONFORPUSHBACK = 'DocumentDistribution' AND B.R.value('@_Type', 'NAME') = 'Borrower'

But what I need, is for the CROSS APPLY the ESIGN node in the path can actually be either ESIGN or ECLOSE. So I am looking to do something like the following (thinking in RegEx terms)...

CROSS APPLY Q.DATA.nodes('LOAN_PUSHBACK_PACKAGE/EVENT_DATA/(ESIGN)|(ECLOSE)/PARTY') AS B(R)

Is there any way to do something like this? I'd really hate to have to repeat the same query twice, just for that simple difference, though maybe XQuery doesn't support options like that?

Actually, I just found I can use an asterisk, which will match both, but I'd LIKE to be able to limit it to those known node values if possible. If not, I guess that will do.

xquery xml sql-server
1 Answers

I think I got it what you need. Here is conceptual example for you.

The XPath predicate expression is checking that the element names at a particular level belong to a sequence of specified names. The <SomethingElse> element is not a member of the sequence, that's why its data is not retrieved.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<LOAN_PUSHBACK_PACKAGE>
    <EVENT_DATA>
        <ESIGN>
            <PARTY _Name="one"/>
        </ESIGN>
        <ECLOSE>
            <PARTY _Name="two"/>
        </ECLOSE>
        <SomethingElse>
            <PARTY _Name="three"/>
        </SomethingElse>
    </EVENT_DATA>
</LOAN_PUSHBACK_PACKAGE>');
-- DDL and sample data population, end

SELECT c.value('@_Name','VARCHAR(20)') AS [Name] 
FROM @tbl
    CROSS APPLY xmldata.nodes('/LOAN_PUSHBACK_PACKAGE/EVENT_DATA/*[local-name(.)=("ESIGN","ECLOSE")]/PARTY') AS t(c);

Output

+------+
| Name |
+------+
| one  |
| two  |
+------+
user_19323110
Related