XML and the SQL 2000 Server, Part 1 - T-SQL support for for XML
(Page 2 of 4 )
This somewhat simplifed picture shows how SQL 2000 supports XML by way of some new constructs for retrieving data from a relational database into various formats of XML, and for inserting data from an XML source into relational data. The two way processability of data either from XML to relational(write) and from relational to XML(read) adds a great deal of flexibility in handling hierarchical and relational data.
For XML SQL 2000 Server has enhanced the Select Statement to retrieve relational data from the tables in XML format. In returning the single column of formatted data in XML, the server runs the query and then XML formats the results, depending on the particular clause, as in the following extensions:
Select From
[where]
[order by]
[for xml (raw|auto[,elements]|
explicit)[,xmldata]
[,binary base64])]
For XML Raw returns rows in a generic row identifier <row>. The intersections for that row with the columns are returned as attributes in name/value pairs. This data may need further processing to map attributes to elements.
For XML raw can have further qualifiers, binary base64 and XMLData. The for xml raw, xmldata returns a schema as well. The next query is run against the pubs database in SQL 2000 server. This may be copied into the Query Analyzer and executed.
The following picture shows the results returned (it starts with the schema first, then the rowset). As the result is stuffed into a column, in the query analyzer's result pane it may look garbled, but it can be copied and pasted into notepad. The results also lack a <root>element, and therefore it is not a well formed XML document, but an XML fragment. In order to view the result in a browser, the returned result has to be enclosed inside <root></root>. The result, as seen in the browser after this, is shown in the next picture (the latter part is truncated to save space).
select pub_id, price, type,ytd_sales,titleauthor.title_id,au_ord
from titles,titleauthor
where titleauthor.title_id=titles.title_id
and price>15
order by pub_id
for xml raw,xmldata
For XML auto returns rows in a more flexible format with reference to tag names. It is essentially element-centric. The row identifier now becomes table name. However, using the qualifier Elements, the For xml auto, elements returns rows qualified by the table name tags, and the columns in each row qualifed by the Column name tag. It is no longer defined by attributes, but rather by elements. The result of running the same query as before but with [for xml raw, xmldata] replaced by [for xml auto] produces the following results as seen in a browser.
For XML Explicit is the mode to use if you require a more flexible way of producing the results of the query. But this requires a more complex query than the other two modes to be run against the database. The syntax required by the for xml explicit mode is demanding because the query has to satisfy the structure of a Universal Table (the row sets mirroring the nesting feature of hierachical data). A very simple example of running a query in the for xml explict mode shows the Universal Table format and the relational parent; child tables are considered here for purposes of illustration.
The tables used in this example are shown in this picture. The two tables have the parent, child relationship and the for XML explicit query will retrieve the data to conform with the Universal Table format. This query is far more complex than the other modes discussed.
The query used to retrieve data that conforms with the Universal Table format is shown here. In order to use the explicit mode, the requirement is the Universal Table format, and since the output is in XML, it has to produce a "well formed document." The first query in this UNION returns the NumId, and this is formatted as Number (third line in the first query, see also output). In the second query NumID is again referenced, because it is required for the join with the Color table.
The column that is produced is the colr column. This is formated as COLOR (see lines 3 and 4 in the first query, and in the order by clause. Also note the output). Running the query by commenting out the for xml explicit clause gives a better insight into this UNION query. It goes without saying that all conditions of forming a UNION must be strictly followed (number of columns, column order, data type, and so forth). This can be extended to deeper nesting orders, although a lot of trial and error may be needed. SQL 2005 removes all these complications by the various enhancements.
select 1 as tag,
null as parent,
nums.NumId as [Number!1!NumId],
null as [COLOR!2!colr]
from nums
union all
select 2,
1,
nums.NumId,
Color.colr
from nums, color
where nums.NumId=color.numid
order by [Number!1!NumId], [COLOR!2!colr]
for xml explicit
The result of this query is as shown here:
Open XML Going from XML to a relational table takes a couple of steps. However, it is a powerful way of carrying out INSERT, DELETE, UPDATE actions by means of carefully constructed XML documents.
Step 1. Prepare a well formed XML document either in the element-centric representation, or in the attribute-centric representation (the code shown here is attribute-centric representation). @idoc is an integer handle, and @doc is a variable that holds the document specified by the set statement. SQL errors 7000 to 7009 describe errors that may be reported during OPENXML operations.
/* T-SQL batch to create a sample XML Document*/
declare @idoc int
declare @doc varchar(1000)
set @doc=
'
<root>
<student studentID="100"
fname="Sam"
lname="Johnson"
ssn="122-23-3456"
phone="(800)-123-2345"/>
<student studentID="101"
fname="Mona"
lname="Lewis"
ssn="532-23-4556"
phone="(789)-321-2345"/>
</root>'
Step 2. Prepare an internal representation of this document by using the stored procedure sp_xml_preparedocument. This takes an input parameter (@doc) and produces an output parameter (@idoc), an internal handle to the document. This parses the document provided using the MSXML parser and keeps it ready to use. This is available as long as connection is not broken to the server, but remember, it uses memory.
[This is the syntax]
sp_xml_preparedocument hdoc OUTPUT
[, xmltext]
[, xpath_namespaces]
Execute sp_xml_preparedocument @idoc OUTPUT, @doc
Step 3. Execute the select statement with the OPENXML rowset provider. The syntax of the OPENXML is as follows:
[This is the syntax]
OpenXML (idoc int [in],rowpattern nvarchar[in],[flags byte[in]])
[WITH (SchemaDeclaration | TableName)]
*Here
flags values 0,1 are for attribute-centric, and 2 for
element-centric documents.
select *
from OPENXML(@idoc, '/root/student',1)with (fname varchar(20), lname varchar(20), ssn varchar(20), phone varchar(20))
Step 4. Make sure you remove the internal representation of the stored procedure to free memory.
Exec sp_xml_removedocument @idoc
Putting them all together, the following query is produced:
/*Step 1: T-SQL batch to create a sample XML Document*/
declare @idoc int
declare @doc varchar(1000)
set @doc=
'
<root>
<student studentID="100"
fname="Sam"
lname="Johnson"
ssn="122-23-3456"
phone="(800)-123-2345"/>
<student studentID="101"
fname="Mona"
lname="Lewis"
ssn="532-23-4556"
phone="(789)-321-2345"/>
</root>'
/*Step 2: Prepare an internal representation of this document*/
Exec sp_xml_preparedocument @idoc output, @doc
/* Step 3: Execute a Select statement that uses OPENXML rowset provider
look under common objects/rowset in the query analyzer objects */
select *
from OPENXML(@idoc, '/root/student',1)
with (fname varchar(20),
lname varchar(20),
ssn varchar(20),
phone varchar(20)
)
Run this query and you should see:
If the with statement is deleted (no schema declared) from the above Select statement, the results are returned in what is called an Edge Table format with all the columns as shown in the next picture.
Next: Overview of XML support in IIS for SQL 2000 Server >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
