Transact SQL :- XQuery – Union

Background

I have been working with an XML document, but never bothered looking at the DTD.

Thankfully

Thankfully today I found I was missing some patterns.

And, so need to quickly understand how to perform Xpath OR matching; I guess it is called XPath Union.

Sample XML Fragment

Here is a sample XML fragment.

xml.20190805.1013PM

Code

Transact SQL

Outline

We issue XPath and use commas (,) to separate each path.

Qualifier Hard coded

SQL


select
          [item] = x.y.query('.')
        , [parent] = x.y.query('..')

from   @basket.nodes
        (
                '

                    /basket/item/department[contains(., "produce")]
                , /basket/item/department[.="hardware"]

                '

        ) as  x(y) 

Image

xml.result.qualifier.hardcoded.01.20190805.1022PM

Qualifier SQL Variable

SQL


declare @type   nvarchar(60)
declare @color  nvarchar(60)

set @type = 'pears'
set @color = 'red'

select
          [item] = x.y.query('.')
        , [parent] = x.y.query('..')

from   @basket.nodes
        (
            '
                /basket/item/type[.=sql:variable("@type")]
              , /basket/item/color[.=sql:variable("@color")]

            '

        ) as  x(y)

 Image

xml.result.qualifier.sql.variable.01.20190805.1010PM

References

  1. Stack Overflow
    • “XQuery [nodes()]: The XQuery syntax ‘union’ is not supported.”
      Link
  2.  MSDN
    • How do I query XML to get xPath union result in SQL Server 2008 +
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s