|
>Home>Learn XQuery>White Papers>Native XML Programming Languages>XML Publishing Functions
Print
XML Publishing Functions
xmlelement()
|
Creates an XML element, allowing the name to be specified.
|
xmlattributes()
|
Creates XML attributes from columns, using the name of
each column as the name of the corresponding attribute.
|
xmlroot()
|
Creates the root node of an XML document.
|
xmlcomment()
|
Creates an XML comment.
|
xmlpi()
|
Creates an XML processing instruction.
|
xmlparse()
|
Parses a string as XML and returns the resulting XML structure.
|
xmlforest()
|
Creates XML elements from columns, using the name of each
column as the name of the corresponding element.
|
xmlconcat()
|
Combines a list of individual XML values to create a
single value containing an XML forest.
|
xmlagg()
|
Combines a collection of rows, each containing a single XML value,
to create a single value containing an XML forest.
|
Let's compare a traditional SQL query with one that uses an XML publishing function. Here is a traditional SQL query that shows customers and their associated projects:
Here is is an excerpt of the result:
Now let's wrap the result in XML elements using xmlelement(), one of the publishing functions:
Each row in the result contains one Customer element. A Customer element looks like this:
xmlforest() is an XML publishing function that creates elements from a list of columns, using the name of the column as the name of the element. Using xmlforest() simplifies many queries significantly. For instance, the following query is equivalent to the previous one:
Now suppose we want to show customers and the projects associated with them. This is easily done with the following SQL query:
However, the result of this query is that shown in the CustomerProject table in the previous section, with one row for each Customer/Project pair. If a customer is associated with more than one project, there will be a row for that customer for each project. Here is a SQL/XML query that creates the XML equivalent to that table:
Here are the results of this query:
This is a straightforward XML translation of the that a SQL result set shown in the previous section, but for most XML applications it is not what we would want. Instead, we want to represent each customer once, with a list of that customer's projects, as shown in the XML output in the previous section. In SQL/XML, this can be done by using a sub-query. Here is a subquery that retrieves the projects associated with each customer. In this subquery we use xmlattributes(), an XML publishing function that creates attributes within an element. The names of the attributes are taken from the names of the columns.
Here is the output of the above sub-query when c.CustId is 4:
This output contains two rows, with one element in each row. Subqueries in SQL/XML are allowed to return only one row; therefore, to return more than one row of values in a SQL/XML subquery, they must be combined to form a single value. xmlagg() is an XML publishing function that produces a forest of elements by collecting the XML values that are returned from multiple rows and concatenating the values to make one value. Here is a query that uses the above subquery to create the XML output from the previous section:
The above query illustrates a very common pattern used to create XML hierarchies using SQL/XML.
|
Learn XQuery
