Just going through some old notes; here are some samples I compiled before that might still be of interest to those of you who are venturing into SQLXML.

The samples below are using miscellaneous numeric and string functions.

Have fun!

Sample XML

DECLARE    @xml XML
SELECT @xml = '
<Employees Dept="IT">
  <Employee Number="1001" Hourly="11.35" FullName="Suzy Jacobs">
	<FName>Suzy</FName>
	<LName>Jacobs</LName>
	<Phone>111-111-1111</Phone>
	<Phone>222-222-2222</Phone>
	<Phone>111-222-1111</Phone>
  </Employee>
  <Employee Number="10" FullName="Bob Willow" ReportsTo="Steve Wong"/>
  <Employee Number="1003" FullName="Tony Carpenter" FName="Tony" LName="Carpenter"/>
  <Employee Number="1004" FullName="Suzy Jacobs" FName="Suzy" LName="Jacobs"/>
  <Staff Number="1003" FName="John"  FullName="John Smith"  LName="Smith" Department="ABC" />
  <MoreStaff Number="1020"  FullName="Ronnie Bates"  FName="Ronnie" LName="Bates" Address="XYZ" Department="ABC" />
</Employees>'

Miscellaneous Functions 1

Using string-length, concat, contains, substring, ceiling, floor, round

<br />
-- miscellaneous functions 1
SELECT
	-- length of "Suzy" 
	-- 4
	@xml.value('string-length((//Employee/FName/text())[1])', 'int') AS 'string-length()',
	-- Suzy===
	@xml.value('concat((//Employee/FName/text())[1], "===")', 'varchar(20)') AS 'concat()',
	-- note that concat is case sensitive
	-- 1
	@xml.value('contains((//Employee/FName/text())[1], "z")', 'bit') AS 'contains()',	
	-- no capital Z in Suzy
	-- 0
	@xml.value('contains((//Employee/FName/text())[1], "Z")', 'bit') AS 'contains()',
	-- 0
	@xml.value('contains((//Employee/FName/text())[1], "x")', 'bit') AS 'contains()',
	-- uz
	@xml.value('substring((//Employee/FName/text())[1], 2,2)', 'varchar(20)') AS 'substring()'

Results

Miscellaneous Functions 2

Using min, max, avg, sum, lower-case, upper-case

-- miscellaneous functions 2
SELECT	
	-- 10
	@xml.value('min(//Employee/@Number)', 'int') AS 'min()',
	-- 1003
	@xml.value('max(//Employee/@Number)', 'int') AS 'max()',
	-- 671.33
	@xml.value('avg(//Employee/@Number)', 'decimal(10,2)') AS 'avg()',
	-- 2014.00
	@xml.value('sum(//Employee/@Number)', 'decimal(10,2)') AS 'sum()',
	-- min/max dont work on strings
	-- NULL
	@xml.value('min(//Employee/@FullName)', 'varchar(30)') AS 'min()',
	-- suzy jacobs
	@xml.value('lower-case((//Employee/@FullName)[1])', 'varchar(30)') AS 'lower-case()',
	-- SUZE JACOBS
	@xml.value('upper-case((//Employee/@FullName)[1])', 'varchar(30)') AS 'upper-case()',
	-- value is 11.35
	-- 12
	@xml.value('ceiling((//Employee/@Hourly)[1])', 'int') AS 'ceiling()',
	-- value is 11.35
	-- 11
	@xml.value('floor((//Employee/@Hourly)[1])', 'int') AS 'floor()',
	-- value is 11.35
	-- 11
	@xml.value('round((//Employee/@Hourly)[1])', 'int') AS 'round()'

Results

Getting distinct values

Using distinct-values

SELECT
	@xml.query(' 
				distinct-values( data(//Employee/@FullName) )
			   ') as 'distinct-values()'	

Results

Be Sociable, Share!

• 
• 

Related posts:

1. SQL Server XML Red Gate Ebook, XQuery Labs …
2. SQLXML : How to Use SQL Server XML Function exist() …
3. SQLXML : How To Save XML Query Results to a File Using BCP …
4. SQLXML : How to Work With XML Elements (or Nodes) in SQL Server …
5. SQLXML : How to Create an XML Schema …
6. SQLXML : How to Check if an XML Schema Already Exists …