SQLXML: Fun with SQLXML XQuery – Miscellaneous Functions – Part 1

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

[sql]
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>’
[/sql]

Miscellaneous Functions 1

Using string-length, concat, contains, substring, ceiling, floor, round
[sql]
<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()’
[/sql]
Results

Miscellaneous Functions 2

Using min, max, avg, sum, lower-case, upper-case
[sql]
— 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()’
[/sql]

Results

Getting distinct values

Using distinct-values
[sql]
SELECT
@xml.query(‘
distinct-values( data(//Employee/@FullName) )
‘) as ‘distinct-values()’
[/sql]

Results

Advertisements

One comment

  • Why this doesn’t work ???

    DECLARE @authorsXML XML

    SET @authorsXML = ‘

    172-32-1176
    White

    10932 Bigge Rd.
    Menlo Park
    CA


    DECLARE @NewFirstName VARCHAR(20)
    SET @NewFirstName = ‘Johnny’
    SET @authorsXML.modify(

    replace value of (/Author/FirstName/text())[1]
    with sql:variable(“@NewFirstName”)
    ‘)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s