Kirk Allen Evans gives an example how to generate a base64 encoded WordML binData element. Just for kicks, here is how the same code would look like in SQL Server 2005 using FOR XML (as in his case, it is not the complete Word document):
WITH
XMLNAMESPACES ('http://schemas.microsoft.com/office/word/2003/wordml' as w)
SELECT img as "w:binData"
FROM OpenRowset(BULK 'c:\temp\test.gif', SINGLE_BLOB) T(img)
FOR XML PATH('w:wordDocument'), TYPE
Oh the joy of declarative programming :-). Here is how you can extract the value (assuming the XML with the base64 encoded value was in a T-SQL variable @x):
SELECT @x.value('declare namespace w="http://schemas.microsoft.com/office/word/2003/wordml";
(/w:wordDocument/w:binData)[1]', 'varbinary(max)')
Note that the value() method will automatically base64 decode (or also hex decode, if the binary value was of type xs:hexBinary) if the target SQL type is a binary type.
FOR XML in SQL Server 2000 puts the burden of generating and maintaining XML namespaces completely on the query writer. XML namespace declaration attributes have to be created like every other attribute with the namespace URI being the column value. Unless the generated XML was in attribute-centric form, this meant that the query has to be written using the EXPLICIT mode. For example, the following query puts the resulting Customer elements and its property elements into the namespace urn:example.com/customer:
SELECT 1 as tag, NULL as parent,
'urn:example.com/customer' as "cust:Customer!1!xmlns:cust",
CustomerID as "cust:Customer!1!cust:CustomerID!element",
ContactName as "cust:Customer!1!cust:ContactName!element"
FROM Customers
FOR XML EXPLICIT
The result of the query is (only first two elements shown)
<cust:Customer xmlns:cust="urn:example.com/customer">
<cust:CustomerID>ALFKI</cust:CustomerID>
<cust:ContactName>Maria Anders</cust:ContactName>
</cust:Customer>
<cust:Customer xmlns:cust="urn:example.com/customer">
<cust:CustomerID>ANATR</cust:CustomerID>
<cust:ContactName>Ana Trujillo</cust:ContactName>
</cust:Customer>
Since namespace declarations are not really attributes in the XML data model, the PATH mode does not allow them to be specified as attributes (you can also not query them as attributes in XPath).
In order to simplify the use of XML namespaces in FOR XML, we have added support for the WITH XMLNAMESPACES clause starting in SQL Server 2005's April CTP. The WITH XMLNAMESPACES clause that has been defined in the SQL:2003 standard is an extension to the WITH clause that is commonly used to define common table expressions and can be placed on top-level SQL statements such as SELECT, INSERT and UPDATE statements and can be used inside a CREATE VIEW statement. It can be used with the RAW, AUTO and PATH mode but not with the XMLSCHEMA and XMLDATA directives, or the EXPLICIT mode. The old way of creating namespaces is still supported for the SQL Server 2000 modes but cannot be mixed with the WITH XMLNAMESPACES clause. To disambiguate the WITH clause from the WITH expression to associate hints, T-SQL statements that preceed WITH clauses need to be terminated with a semi-colon (;). The following query places the customer and order data into different namespaces and adds a root node in a default namespace.
WITH XMLNAMESPACES (
DEFAULT 'urn:example.com/doc'
, 'urn:example.com/customer' as "c"
, 'urn:example.com/order' as "o"
)
SELECT CustomerID as "@ID",
(SELECT OrderID as "@OrderID"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('o:Order'), TYPE
) as "c:Orders",
CompanyName as "c:CompanyName",
ContactTitle as "c:ContactName/@ContactTitle",
ContactName as "c:ContactName/text()",
PostalCode as "c:Address/@ZIP",
Address as "c:Address/c:Street",
City as "c:Address/c:City"
FROM Customers
FOR XML PATH('c:Customer'), ROOT('doc')
As the following partial result shows, the XML namespace declarations are currently added at the top-level elements of every FOR XML selection:
<doc xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc">
<c:Customer ID="ALFKI">
<c:Orders>
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10643" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10692" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10702" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10835" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10952" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="11011" />
</c:Orders>
<c:CompanyName>Alfreds Futterkiste</c:CompanyName>
<c:ContactName ContactTitle="Sales Representative">Maria Anders</c:ContactName>
<c:Address ZIP="12209">
<c:Street>Obere Str. 57</c:Street>
<c:City>Berlin</c:City>
</c:Address>
</c:Customer>
...
The above query used the DEFAULT clause for adding a default namespace. Note that if there are nested XML documents with no default namespace included in the result, a slight performance penalty has to be paid to make sure that they preserve their absence of a default namespace.
Finally, the WITH XMLNAMESPACES clause can also be used to provide namespace bindings for the XQuery and XML DML methods on the XML data type. This can especially be useful if several methods are invoked in the same SQL expression. For example, we can now write the following expression from the TechEd 2004 demo:
WITH XMLNAMESPACES ('urn:example/customer' as "c")
SELECT N1.customer.query('.') as customer,
N1.customer.value('c:name[1]', 'nvarchar(20)') as CustomerName,
N2."order".value('@id', 'int') as OrderID,
N1.customer.value('../@id', 'nvarchar(5)') as DocID
FROM XMLdoc
cross apply XMLdoc.doc.nodes( '/c:doc/c:customer') as N1(customer)
cross apply N1.customer.nodes( 'c:order') as N2("order")
[Most of this weblog posting will at some point be added to the FOR XML whitepaper on MSDN]
As many of you probably already have heard, we have publicly released the April Community Tech Preview (CTP) and have also announced that we are continuing with CTPs instead of doing a Beta3. I agree with Adam: This is good for getting both more and better feedback from our customers. And it allows us to be more flexible with providing fixes and new developments earlier to customers. You can get the normal SQL Server Developer Edition through the MSDN Subscription download and as always SQL Server Express (and VisualStudio 2005 Beta2) from the MSDN site directly.
Now let me continue the tradition to report on what has changed with this CTP over the previous (February) CTP in the area of XML:
Breaking Changes:
- Ownership chaining is enforced for XML Schema Collections.
- The names of many of the XML schema collection related catalog views have changed – see the change list for XML catalog views for details.
Basically, we have added _schema to the catalogs that do not yet have it as part of their name. For example, the sys.xml_components table is now called sys.xml_schema_components.
- Closer alignment with the W3C XQuery July 2004 working draft and other XQuery changes:
- The sequence type syntax and semantics (used in instance of) got aligned to a subset of the July 2004 WD: For example, we require instance of(<name>, <type> ?) instead of instance of(<name>, <type> nillable), and we do not take schema components for element names into account anymore.
- The functions get-local-name-from-QName() and get-namespace-uri-from-QName() have been renamed to local-name-from-QName() and namespace-uri-from-QName() respectively.
- XQuery comment delimiters (not constructors) are now (: and :) respectively instead of {-- and --}.
- The type xdt:untypedAny has been renamed to xdt:untyped.
- We now require a ‘;’ after each XQuery prolog entry. So please make sure that you are adding them now!
- Support for lt, gt, le, ge on Gregorian types has been removed.
- fn:concat now only supports 2 to n parameters as prescribed by the working draft (instead of 0 to n).
- End-of-line and whitespace normalization for attribute content and literal string values inside XQuery expressions in attribute content when being used in a direct element constructor have been aligned with the July WD.
- sum() on empty sequence returns now 0 instead of ().
- Grammar precedences of instance of, cast as and unary +/- have been aligned to July 2004 WD.
- We fixed the comparison semantics on xs:date: xs:date("2005-01-19+14:00") < xs:date("2005-01-18-14:00") will now return true.
- We aligned our static typing rules across the XQuery aggregators (min/max/sum/avg), the order by clause, and distinct-values(). The new rules now require that the expressions passed to these functions and the order by clause statically have a single primitive base type. The acceptable base types depend on the agregators. For example, sum() supports the 3 numeric base types or xdt:untypedAtomic (which will be treated in this case as xs:double), but you cannot have a static mixture of numeric types with different base types. Also, distinct-values() already since the February CTP performs atomization implicitly.
- FOR XML: You cannot generate an inline schema with target namespace http://www.w3.org/XML/1998/namespace anymore (well, you shouldn't have done that before anyway, but now we will error).
- Namespace URI values in XQuery, the XML datatype parser and FOR XML (but not OpenXML) will now get whitespace normalized according to the xs:anyURI rules. This means that leading and trailing whitespace gets removed and whitespace inside will be collapsed. Note: URIs should not contain whitespaces in the first place!
- The nodes() method result cannot be referenced anymore inside ORDER BY or GROUP BY without applying an XML datatype method.
Non-Breaking changes:
The next CTP will bring a few more XQuery function alignments and more performance improvements as well as some additional whitespace preservation measures. Stay tuned!