imported>Citation bot: Alter: url. URLs might have been anonymized. | Use this bot. Report bugs. | Suggested by AManWithNoPlan | #UCB_CommandLine

2023-03-28T13:28:05Z

Alter: url. URLs might have been anonymized. | Use this bot. Report bugs. | Suggested by AManWithNoPlan | #UCB_CommandLine

New page

'''SQL/XML''' or '''XML-Related Specifications''' is part 14 of the [[Structured Query Language]] (SQL) specification. In addition to the traditional predefined SQL data types like <code>NUMERIC, CHAR, TIMESTAMP, ... </code> it introduces the predefined data type <code>XML</code> together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL [[database]].

* [[SQL:2003|SQL:2003-14]] defines the basic datatype, mappings, predicates and functions.
* [[SQL:2006|SQL:2006-14]], [[SQL:2008|SQL:2008-14]] and [[SQL:2011|SQL:2011-14]] expands the older versions and approaches to [[XQuery]].{{r|funderburk}}

==Specification==
The specification defines the data type <code>XML</code>, functions for working with XML, including element construction, mapping data from relational tables, combining XML fragments, and embedding [[XQuery]] expressions in [[SQL]] statements. Functions which can be embedded include XMLQUERY (which extracts XML or values from an XML field) and XMLEXISTS (which predicates whether an XQuery expression is matched).

Further information and examples of the SQL/XML functions are provided in the external links below{{r|wagner|melton|eisenberg}}.

==Standard compliance==
The result of Wagner's objective evaluation of the ''SQL/XML'':2006 standard compliance of Oracle 11g Release 1, MS SQL Server 2008 and MySQL 5.1.30 is shown in the following table{{r|wagner}}, to which the data for PostgreSQL 9.1,<ref>[http://www.postgresql.org/docs/9.1/static/features.html PostgreSQL Conformance with ISO 9075-14 (SQL/XML)], at PostgreSQL 9.1 documentation.</ref><ref>[http://www.postgresql.org/docs/current/static/functions-xml.html PostgreSQL 9.1 XML functions], at PostgreSQL 9.1 documentation.</ref> and IBM DB2 has been added:

{| class="wikitable" style="text-align:center;"
|-
! !! Oracle 11g Release 1 !! IBM DB2 9.7 !! MS SQL Server 2008 !! MySQL 5.1.30 !! PostgreSQL 9.1
|-
! Datatype XML
| {{partial}} (Oracle entitles the data type 'XMLType' instead of 'XML') || High || High || {{No}} || {{Partial}}
|-
! SQL/XML predicates
| High || High || {{Partial}} || {{No}} || {{Partial}}
|-
! SQL/XML functions
| High || High || {{Partial}} || Low || High
|-
! [[XQuery]] augmentation
| {{yes}} || {{yes}} || {{yes}} || {{no}} || {{no}}
|}

==Examples==
The sample SQLXML query below has SQLXML type as output(tested on DB2 9.7 and Oracle 11g):
<syntaxhighlight lang="sql">
SELECT XMLELEMENT(NAMEs "PhoneBook", -- root element name
XMLAGG( -- aggregation over the rows
XMLELEMENT(NAME "Contact",
XMLATTRIBUTES(cust.FIRST_NAME AS "Name",
cust.TEL)
)
)
)
FROM TMP.CUSTOMER AS cust;
</syntaxhighlight>

And the output:
<syntaxhighlight lang="xml">
<PhoneBook>
<Contact Name="Daniel" TEL="788255855"/>
<Contact Name="Martin" TEL="889665447"/>
<Contact Name="Eva" TEL="111222333"/>
<Contact Name="Alena" TEL="444555666"/>
<Contact Name="Oliver" TEL="777888999"/>
<Contact Name="George" TEL="444882446"/>
<Contact Name="Jamie" TEL="123456789"/>
</PhoneBook>
</syntaxhighlight>
Samples are taken from javalobby article{{r|kec}}.

==References==
<references>
<ref name="eisenberg">{{Citation
|title = SQL/XML is Making Good Progress
|author = Eisenberg, Andrew
|author2 = Melton, Jim
|journal = SIGMOD Record
|volume = 31
|number = 2
|year = 2002
|url = http://www.sigmod.org/publications/sigmod-record/0206/standard.pdf
|doi = 10.1145/565117.565141
|accessdate = 14 November 2011
|pages=101
|citeseerx = 10.1.1.109.4867
}}</ref>

<ref name="funderburk">{{Citation
|title = XML programming with SQL/XML and XQuery
|author = Funderburk, J. E.
|author2 = Malaika, S.
|author3 = Reinwald, B.
|journal = IBM Systems Journal
|volume = 41
|number = 4
|year = 2002
|url = http://ict.udlap.mx/people/carlos/is346/files/reinwald.pdf
|doi = 10.1147/sj.414.0642
|accessdate = 14 November 2011
|pages=642–665
}}</ref>

<ref name="melton">{{Citation
|title = Advancements in SQL/XML
|author = Eisenberg, Andrew
|author2 = Melton, Jim
|journal = SIGMOD Record
|volume = 33
|number = 3
|year = 2004
|url = http://www.sigmod.org/publications/sigmod-record/0409/11.JimMelton.pdf
|doi = 10.1145/1031570.1031588
|accessdate = 14 November 2011
|pages=79
}}</ref>

<ref name="wagner">{{Citation
|title = SQL/XML:2006 - Evaluierung der Standardkonformität ausgewählter Datenbanksysteme
|chapter = 1. Auflage
|author = Wagner, Michael
|year = 2010
|publisher = Diplomica Verlag
|isbn = 978-3-8366-9609-8
|url = https://books.google.com/books?id=HJH-WAPNcNgC
}}</ref>
<ref name="kec">{{Citation
|title = SQLX - From DB Straight to XML and Back
|author = Kec, Daniel
|year = 2013
|publisher = Javalobby.org
}}</ref>
</references>

==External links==
* [http://www.postgresql.org/docs/current/static/functions-xml.html SQL/XML on PostgreSQL]
* [http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb13gen.htm#g1047191 SQL/XML on Oracle 11g]

{{SQL}}

{{DEFAULTSORT:SQL XML}}
[[Category:SQL]]

{{database-stub}}

SQL/XML - Revision history

imported>Citation bot: Alter: url. URLs might have been anonymized. | Use this bot. Report bugs. | Suggested by AManWithNoPlan | #UCB_CommandLine