Need to work with xml column in SQL Server, and you don’t have a clue what’s XQuery is ?
Well, that was my situation, here’s some basic things you could use if you need to.
XQuery is a query and functional programming language that is designed to query and transform collections of structured and unstructured data, usually in the form of XML, text and with vendor specific extensions other data formats (JSON, binaries, …). (http://en.wikipedia.org/wiki/XQuery)
That said, let’s see what we can do with that in SQL Server. I’ll just present you the different functions available to work with.
query() : works against an xml column, returns xml
SELECT Properties.query('/Properties/Data')
FROM [Table] where ID = 2584
If you have a same node in different hierarchies (for instance Data in /Properties and in /Metadata), you can leave a blank between 2 slashes :
SELECT Properties.query('//Data')
FROM [Table]
query() : http://msdn.microsoft.com/fr-fr/library/ms191474.aspx
exist() : similar to the WHERE statement, but in a xml
Retrieve node /Properties/Data for all records :
... WHERE Properties.exist('/Properties/Data') = 1
... WHERE Properties.exist('/Properties[Data]') = 1 -- equivalent
To get only the nodes whose value is a given one (3214) :
... WHERE Properties.exist('/Properties/Data/Column[(ID=3214)]') = 1
To test the value of an attribute, for instance, here, the “xsi:type” :
<Data xsi:type="Pie">
<Data xsi:type="XY">
Use the prefix “@” in front of the attribute name :
... WHERE Properties.exist('/Properties/Data[@xsi:type="Pie"]') = 1
exist(): http://msdn.microsoft.com/fr-fr/library/ms189869.aspx
value() : select a value in your xml and returns a value cast into the datatype you want.
This function takes 2 params :
– xquery : an xquery that returns only one value, for that, use the syntax “(/path/value)[1]” otherwise you will end up with the error “‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *'”
– the type you want to return, between quotes (internally, it uses CONVERT)
SELECT Properties.value('(/Properties)[1]', 'nvarchar(20)') ...
SELECT Properties.value('(/Properties/Column/ID)[1]', 'int') ...
value(): http://msdn.microsoft.com/fr-fr/library/ms178030.aspx
modify() : update an xml column.
It takes a string in param, that is not xquery, but XML DML (XML Data Modification Language).
This language is an extension of xquery adding some keywords such as ‘insert’ ‘delete’ ‘replace value of’, used to manipulate the xml.
modify : delete
To remove all <Useless>…<Useless> from its parent node :
update [Table]
set Properties.modify('delete /Properties/Useless')
If you want to remove the second occurence, use :
update [Table]
set Properties.modify('delete (/Properties/Useless)[2]')
Even if there is no match, SQL Server returns : (1337 row(s) affected)
To delete an attribute :
update [Table]
set Properties.modify('delete (/Properties/Useless/@myattr)[1]')
-- to delete all attributes :
set Properties.modify('delete (/Properties/Useless/@*)')
To delete all inner nodes :
update [Table]
set Properties.modify('delete (/Properties/Useless/*)')
To delete severals nodes in the same query :
update [Table]
set Properties.modify('delete ( /Properties/Useless/, /Properties/YouToo, /Properties/AndYou )')
modify : insert
To add it back (just one) :
update [Table]
set Properties.modify('insert <Useless></Useless> into (/Properties)[1]')
‘into’ could be prefixed by ‘as first’ or ‘as last’ according to where you want to add it into the child. (‘as last into’)
But you can insert your node, not inside the target, but after or before it : use ‘after or ‘before’ instead of ‘into’.
In short : {as first | as last} into | after | before
Again, you have to specify something that is a single node using (..)[1] otherwise you’ll get :
The target of ‘insert’ must be a single node, found ‘element(Properties,xdt:untyped) *’
If you want to insert a value into a node, you have to use the text{} function :
-- <Score />
update [Table]
set Properties.modify('insert text{"42"} into (/Properties/Score)[1]')
-- <Score>42</Score>
To add an attribute to a node, use ‘insert attribute {}’ :
-- <Score />
update [Table]
set Properties.modify('insert attribute gameover {"true"} into (/Properties/Score)[1]')
-- <Score gameover="true" />
To change its value :
-- <Score />
update [Table]
set Properties.modify('replace value of (/Properties/Time/@test)[1] with ""')
-- <Score />
modify : replace value of … with …
This function takes 2 params, the first is the path where to replace, the second is the value, it makes sense.
The first param needs a single node (“(/…)[n]”) otherwise : The target of ‘replace’ must be at most one node, found ‘element(Useless,xdt:untyped) *’
The second param should be just a string. If you try some metadata like with <score></score>, you’ll get an error : The target of ‘replace value of’ must be a non-metadata attribute or an element with simple typed content, found ‘element(,xdt:untyped) ?’
-- <Score>573</Score>
update [Table]
set Properties.modify('replace value of (/Properties/Score/text())[1] with "42.0"')
-- <Score>42</Score>
This will work if your node already has a value, otherwise, it won’t do a thing (for instance if you have <Score />).
For this case, use the insert text{} :
-- <Score />
update [Table]
set Properties.modify('insert text{"42"} into (/Properties/Score)[1]')
-- <Score>42</Score>
If you want to clear a node value, use “with “”)
-- <Score>42</Score>
update [Table]
set Properties.modify('replace value of (/Properties/Score/text())[1] with ""')
-- <Score />
Now, let’s introduce more complex cases that uses functions or references others parts of the same xml.
Reference other part of the XML
Insert nodes which values are from another part of the xml :
-- <Target />
set Properties.modify('insert /Properties/Source into (/Properties/Target)[1]')
-- <Target><Source>google</Source><Target>
You can see that the whole node had been put into the target. If you want the value only :
-- <Target />
... set Properties.modify('insert /Properties/Source/text() into (/Properties/Target)[1]')
-- <Target>google<Target>
You can count how many nodes match a xquery :
-- <Target />
... set Properties.modify('insert text{ count(/Properties/Data/*) } into (/Properties/Target)[1]')
-- <Target>7<Target>
Reference the record columns
You have a “Width” column in your table, you want to put it into the xml :
-- <Target />
... set Properties.modify('insert ( <Width>{ sql:column("Width") } </Width> ) into (/Properties/Target)[1]')
-- <Target><Width>573</Width><Target>
Moreover, there are functions available to modify your strings, for instance the well known ‘substring’. Let’s combine that with the text{} function to add a value (not a node) to our target.
-- <Target />
... set Properties.modify('insert text{substring(sql:column("Name"),1,3)} into (/Properties/Target)[1]')
-- <Target>Joh<Target>
If statement
Finally, know that you can do small function directly inside your xml dml such as :
-- <Target />
... set Properties.modify('insert(
if (count(/Properties/Data/*) > 3)
then
attribute complex {"true"}
else
attribute complex {"false"},
attribute type {upper-case(sql:column("Name"))},
text {/Properties/Title/text()}
) into (/Properties/Target)[1]')
-- <Target complex="false" type="JOHN">my title<Target>
FLWOR
What is that ? Well, it stands for : for, let, where, order by, and return.
For instance, if you want to query your xml and returns xml from it :
SELECT Properties.query('
for $data in /Accounts/*
return
<User>
<ID>{ data($data/Guid) }</ID>
<Fullname>{ data($data/Firstname), data($data/Lastname) }</Fullname>
</User>
')
FROM [Table]
Severals things in this code :
– creates … for every nodes in /Accounts
– creates 3434 for each , based on the Guid node text : data(node) is equivalent to node/text()
– creates John Doe from the concatenation of the firstname ans lastname nodes text.
There is a ‘where’ clause, and an ‘order by’ too.
FLWOR Statement and Iteration : http://msdn.microsoft.com/en-us/library/ms190945.aspx
Now that you know the basics, read the documentation, there is no better place to know more. 😉
modify: http://msdn.microsoft.com/fr-fr/library/ms187093.aspx
XML Data Modification Language (XML DML) : http://msdn.microsoft.com/fr-fr/library/ms177454.aspx
insert: http://msdn.microsoft.com/fr-fr/library/ms175466.aspx
delete: http://msdn.microsoft.com/fr-fr/library/ms190254.aspx
replace value of: http://msdn.microsoft.com/fr-fr/library/ms190675.aspx
XQuery basics : http://msdn.microsoft.com/fr-fr/library/ms190262.aspx
Available functions to work against xml column : http://msdn.microsoft.com/fr-fr/library/ms189254.aspx
Functions on String Values (length, concat, convert, contains, substring..): http://msdn.microsoft.com/fr-fr/library/ms178064.aspx
ClicData Coder 