title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | monikerRange |
---|---|---|---|---|---|---|---|---|
Get URL references to BLOB data with sql:encode (SQLXML) |
Learn how to request an URL reference to BLOB data by specifying the sql:encode annotation in SQLXML 4.0. |
MikeRayMSFT |
mikeray |
03/14/2017 |
sql |
xml |
reference |
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database] In an annotated XSD schema, when an attribute (or element) is mapped to a BLOB column in Microsoft [!INCLUDEssNoVersion], the data is returned in Base 64-encoded format within XML.
If you want a reference to the data (a URI) to be returned that can be used later to retrieve the BLOB data in a binary format, specify the sql:encode annotation. You can specify sql:encode on an attribute or element of simple type.
Specify the sql:encode annotation to indicate that a URL to the field should be returned instead of the value of the field. sql:encode depends on the primary key to generate a singleton select in the URL. The primary key can be specified using the sql:key-fields annotation.
The sql:encode annotation can be assigned the "url" or the "default" value. A value of "default" returns data in Base 64-encoded format.
The sql:encode annotation cannot be used with sql:use-cdata or on the ID, IDREF, IDREFS, NMTOKEN, or NMTOKENS attribute types. It can also not be used with XSD fixed attribute.
Note
BLOB-type columns cannot be used as a part of a key or foreign key.
To create working samples using the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.
In this example, the mapping schema specifies sql:encode on the LargePhoto attribute to retrieve the URI reference to a specific product photo (instead of retrieving the binary data in Base 64-encoded format).
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ProductPhoto" sql:relation="Production.ProductPhoto"
sql:key-fields="ProductPhotoID" >
<xsd:complexType>
<xsd:attribute name="ProductPhotoID" type="xsd:int" />
<xsd:attribute name="LargePhoto" type="xsd:string" sql:encode="url" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
-
Copy the schema code above and paste it into a text file. Save the file as sqlEncode.xml.
-
Copy the following template and paste it into a text file. Save the file as sqlEncodeT.xml in the same directory where you saved sqlEncode.xml.
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="sqlEncode.xml"> /ProductPhoto[@ProductPhotoID=100] </sql:xpath-query> </ROOT>
The directory path specified for the mapping schema (sqlEncode.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:
mapping-schema="C:\SqlXmlTest\sqlEncode.xml"
-
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
This is the result:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ProductPhoto ProductPhotoID="100"
LargePhoto="dbobject/Production.ProductPhoto[@ProductPhotoID="100"]/@LargePhoto" />
</ROOT>