SQLServer – Xquery – Using local-name

Background

As always too little time to study, but I need to get a node’s name.

And, everything points at using local-name.

 

What is local-name?

I like mozilla.org’s  definition & example.  It is here:

Definition

The local name of a node is that part of the node’s qualified name that comes after the colon. Qualified names are typically used in XML as part of the namespace(s) of the particular XML documents.

Sample

For example, in the qualified name ecomm:partners, partners is the local name and ecomm is the prefix:

 

Xml

Body

Here is a sample of the XML that we will be working with.

<hub>
  
<country>
United States

  <state>California
	  <city population="4 million">Los Angeles</city>
  </state>


  <state>Illinois
	  <city population="2.7 million">Chicago</city>
  </state>

  <state>New York
	  <city population="8.6 million">New York City</city>
  </state>


  <state>Texas
  <city population="2.3 million">Houston</city>
  <city population="1.3 million">Dallas</city>
  <city population="950 thousand">Austin</city>
  </state>


 </country>
  
<country>
Canada
  <province>Ontario
  <city population="2.8 million">Toronto</city>
  </province>

  <province>British Columbia
  <city population="2.5 million">Vancouver</city>
  </province>


 </country>
  
</hub>

SQL

Query



declare @xmlHub xml
  
set @xmlHub =
'<hub>
  
<country>
United States

  <state>California
      <city population=''4 million''>Los Angeles</city>
  </state>


  <state>Illinois
      <city population=''2.7 million''>Chicago</city>
  </state>

  <state>New York
      <city population=''8.6 million''>New York City</city>
  </state>


  <state>Texas
  <city population=''2.3 million''>Houston</city>
  <city population=''1.3 million''>Dallas</city>
  <city population=''950 thousand''>Austin</city>
  </state>


 </country>
  
<country>
Canada
  <province>Ontario
  <city population=''2.8 million''>Toronto</city>
  </province>

  <province>British Columbia
  <city population=''2.5 million''>Vancouver</city>
  </province>


 </country>
  
</hub>
'
  
--select [hubList] = @xmlHub
  
select
  
     [country]
        =
           (
                r.[node].value
                (
                   'text()[1]'
  
                    , 'varchar(80)'
                )
  
            )


    , [stateOrProvince]
        =
           (
                r2.[node2].value
                (
                   'text()[1]'
  
                    , 'varchar(80)'
                )
  
            )

/*
    , [stateOrProvinceAlt]
        =
           (
                r3.[node3].value
                (
                   '../text()[1]'
  
                    , 'varchar(80)'
                )
  
            )

*/
  
    , [IsStateOrProvince?]
            =  (
                    cast
                    (
  
                        (
  
                            r2.[node2].query
                                (
                                'local-name(.)'
                                )
  
                        )
  
						as varchar(80)
					)	
  
				)

    , [IsStateOrProvinceAlt?]
            =  (
                    cast
                    (
  
                        (
  
                            r3.[node3].query
                                (
                                'local-name(..)'
                                )
  
                        )
  
                    as varchar(80)
                )
  
            )

  
    , [city]
        =
           (
                r3.[node3].value
                (
                   'text()[1]'
  
                    , 'varchar(80)'
                )
  
            )
 
    , [population]
        =
           (
                r3.[node3].value
                (
                   '@population'
  
                    , 'varchar(80)'
                )
  
            )

--from @xmlHub.nodes('/hub/country') as r(node)
from @xmlHub.nodes('//country') as r(node)


/*
	Msg 9335, Level 16, State 1, Line 138
	XQuery [r.node.nodes()]: The XQuery syntax 'union' is not supported.
*/
--cross apply r.node.nodes('(state | region)') as r2(node2)


--cross apply r.node.nodes('*') as r2(node2)
cross apply r.node.nodes('(state, province)') as r2(node2)


--cross apply r2.node2.nodes('*/city') as r3(node3)
cross apply r2.node2.nodes('city') as r3(node3)

order by
      [country]
    , [stateOrProvince]
    , [city]


 

Output

 

Explanation

  1. XML
    • XML Data in SQL
      • Population
        • Population is represented as a string ( 5 million, 2.3 million, etc ) and we have to escape using two quotes
  2. SQL Query
    • Shred XML
      • We use the nodes method a couple of times
        • Country
          • The first nodes method splits our XML into Country fragments
            • The Xquery passed to the nodes method is
              • //Country
              • /hub/country will work just as well
            • The result of the shredding is projected out as Row r and column n
        • State or Region
          • The second nodes method splits the Country fragment into City or Region
            • Here are sample XQueries
              • (state, province)
                • Means we can parse on State or Province
              • *
                • Place holder for any node
        •  City
          • The third nodes method splits the result of the second node into a city fragments
            • The XQuery passed in is
              • city
    • Projected Columns
      • Country
        • Here are sample clause to extract the Country
          • r.[node].value(‘text()[1]’, ‘varchar(80)’ )
      • City
        • Sample clauses
          • r2.[node2].value(‘text()[1]‘, ‘varchar(80)’)
            • Uses second node()’s
          • r3.[node3].value(‘../text()[1]‘, ‘varchar(80)’ )
            • Uses third node()’s parent
      • Is State or Province
        • Sample Clause
          • cast( ( r2.[node2].query( ‘local-name(.)’ )) as varchar(80))
          • cast( ( r3.[node3].query( ‘local-name(..)’ )) as varchar(80))
      • City
        • Sample Clause
          • r3.[node3].value( ‘text()[1]‘ , ‘varchar(80)’ )
      • Population
        • Sample Clause
          • r3.[node3].value( ‘@population‘ , ‘varchar(80)’ )

References

  1. StackOverflow
    • What is the difference between name() and local-name()?
      Link
  2. Mozilla.Org
    • MDN web docs
      • Node.localName
        Link
  3. Microsoft
    • Docs / SQL / T-SQL / Data types / XML
      • Nodes method
        • Nodes() Method
          Link
      • value() method
        Link
  4. Codegumbo
    • SQL Server XQuery: .value() method
      Link
    • SQL Server XQuery: .query() method
      Link
  5. Priscilla Walmsley
    • Definitive XML Schema 2e
      XQuery
      Priscilla Walmsley (pwalmsley@datypic.com)
      ISBN: 1491915102
      2nd edition, 2015, O’Reilly Media, Inc.
    • Chapters

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 )

w

Connecting to %s