Mixed content (part 3)
It’s been a few weeks since my last post about mixed content so let’s pick up right where we left off.
This time we will look at DML operations on mixed content. We’ll keep using the XML schema as well as the table I created in the first post of this series.
Let’s insert an instance to work with.
INSERT INTO T VALUES (3, CONVERT(XML(SC_Letter), '<l:letter xmlns:l="urn:letter" xmlns:t="urn:letter:tags">
<l:date>2006-10-15-08:00</l:date>
<l:sender>
<l:FirstName>James</l:FirstName>
<l:LastName>Kirk</l:LastName>
<l:Line1>1234 Elm Street</l:Line1>
<l:City>Redmond</l:City>
<l:ZIPCode>98052</l:ZIPCode>
<l:State>WA</l:State>
</l:sender>
<l:recipient>
<l:FirstName>Jean-Luc</l:FirstName>
<l:LastName>Picard</l:LastName>
<l:Line1>13 rue des Lilas</l:Line1>
<l:City>Ronchin</l:City>
<l:ZIPCode>59555</l:ZIPCode>
<l:Country>France</l:Country>
</l:recipient>
Dear Sir,
I received your letter dated <t:date>2006-10-10+00:00</t:date> this morning. It would be my pleasure to attend the annual symposium <t:title>XML database systems in class-B Federation Spacecrafts</t:title>.
Please contact my assistant <t:person>Mr. Spock</t:person> at <t:email>spock@space-federation.org</t:email> to make all necessary arrangements.
Sincerely,
James T. Kirk
</l:letter>', 1))
go
DML operations on elements
There are no behavior differences between element-only content and mixed content. You can insert, delete, and replace the value of elements provided the resulting instance validates against the schema.
For example, let’s update the name of the assistant mentioned in the letter.
UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";
declare namespace t = "urn:letter:tags";
replace value of (/l:letter//t:person[data(.) = "Mr. Spock"])[1] with "Capt. Spock"')
WHERE iCol = 3
go
You can query the instance back and see for yourself that the value of the element has been updated.
We could also decide not to mention the assistant by name and delete the element.
UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";
declare namespace t = "urn:letter:tags";
delete (/l:letter//t:person[data(.) = "Capt. Spock"])[1]')
WHERE iCol = 3
go
If you query the instance back you can see that the letter now says
Please contact my assistant at <t:email>spock@space-federation.org</t:email> to make all necessary arrangements.
Something very interesting happened here that is not immediately visible. If you remember, in our previous posts, we used the following query to examine the nodes in our instance
SELECT x.query('.'), x.query('if (. instance of text()) then "text" else
if (. instance of element()) then "element" else ""')
FROM T
CROSS APPLY xmlCol.nodes('/*:letter[1]/node()') AS Tref(x)
WHERE iCol = 3
Had we run this query prior to deleting the <person> element, we would’ve seen this as part of our results (partial results printed to save space)
.Please contact my assistant |
text |
<p1:person xmlns:p1="urn:letter:tags">Capt. Spock</p1:person> |
element |
at |
text |
<p1:email xmlns:p1="urn:letter:tags">spock@space-federation.org</p1:email> |
element |
to make all necessary arrangements.Sincerely,James T. Kirk |
text |
Now that we’ve deleted the element, the results look like this (partial results again)
.Please contact my assistant at |
text |
<p1:email xmlns:p1="urn:letter:tags">spock@space-federation.org</p1:email> |
element |
to make all necessary arrangements.Sincerely,James T. Kirk |
text |
Since we delete an element that was between two text nodes, those nodes merged to form only one.
DML operations on text nodes
Text nodes can be updated, inserted or deleted.
First, let’s replace the value of a text node.
UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";
declare namespace t = "urn:letter:tags";
replace value of /l:letter[1]/text()[6] with ". I suggest that you talk to my asisstant, who can be contacted at "')
WHERE iCol = 3
go
You can query the instance to see that the text of the letter was modified appropriately.
Now let’s insert a text node after an existing one
UPDATE T SET xmlCol.modify(' declare namespace l = "urn:letter";
declare namespace t = "urn:letter:tags";
insert text{"
	
	PS: Live long and prosper."} as last into /l:letter[1]')
WHERE iCol = 3
go
If you look at the nodes, you’ll see that the new text node we inserted merged with the one preceding it to form one text node so that all text between two elements tags counts as only one text node (partial results printed below)
. I suggest that you talk to my asisstant, who can be contacted at |
text |
<p1:email xmlns:p1="urn:letter:tags">spock@space-federation.org</p1:email> |
element |
to make all necessary arrangements.
Sincerely, James T. Kirk
PS: Live long and prosper. |
text |
-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.