SELECT ... FOR XML

[NEW in v4.0]

This feature of Valentina database provides the ability to get result of the SELECT query as XML.

SELECT ... FOR XML [RAW|AUTO] [,] [ELEMENT | ELEMENTS] [[WITH | USE] CDATA]

Returns the one-row cursor consist of single TEXT field. The field keeps XML result of the query.

• ELEMENT

If mentioned all data are placed as child elements else - as attributes.

• RAW

Make an XML entity from each row of the result set; do not attempt to construct hierarchies. Each row's data is enclosed in a <ROW/> element and each column is either an attribute or child element.

• AUTO

A hierarchy is constructed with one level for each table of the join for which at least one column is selected. The table whose column is first mentioned in the selection will be the topmost element, the next table its child, etc. Each level of the tree will consist of one type of element. A parent element will have multiple children if consecutive rows do not differ in the column values coming from the parent element. When a table's column values differ from the previous row, the element and all children thereof are closed and a new element is started, with children filled out from other columns of the result set.

• CDATA

Some string fields may contain invalid XML symbols. CDATA clause requires to put such data wrapped in <![CDATA[]]>. So you always get a valid XML. Sure, there is some overhead to detect such symbols - so if you feel lucky you may ignore CDATA clause but in this case no checks performed (data goes to XML as is) and you may get an invalid XML .

Example AUTO

SELECT * FROM Person FOR XML AUTO

<Person Name="Smith" personID="1" />
<Person Name="Ivanov" personID="2" />
<Person Name="Petrov" personID="3" />
<Person Name="Test1" personID="6" />
<Person Name="Test2" personID="5" />

Example RAW

SELECT * FROM Person FOR XML RAW

<Person Name="Smith" personID="1" />
<Person Name="Ivanov" personID="2" />
<Person Name="Petrov" personID="3" />
<Person Name="Test1" personID="6" />
<Person Name="Test2" personID="5" />

Example AUTO + ELEMENT

SELECT * FROM Person FOR XML AUTO ELEMENT

<Person>
    <Name>Smith</Name>
    <personID>1</personID>
</Person>
<Person>
    <Name><Ivanov</Name>
    <personID>2</personID>
</Person>
<Person>
    <Name>Petrov</Name>
    <personID>3</personID>
</Person>
<Person>
    <Name>Test1</Name>
    <personID>6</personID>
</Person>
<Person>
    <Name>Test2</Name>
    <personID>5</personID>
</Person>

Example RAW + ELEMENT

SELECT * FROM Person FOR XML RAW ELEMENT

<ROW>
    <Name>Smith</Name>
    <personID>1</personID>
</ROW>
<ROW>
    <Name>Ivanov</Name>
    <personID>2</personID>
</ROW>
<ROW>
    <Name>Petrov</Name>
    <personID>3</personID>
</ROW>
<ROW>
    <Name>Test1</Name>
    <personID>6</personID>
</ROW>
<ROW>
    <Name>Test2</Name>
    <personID>5</personID>
</ROW>

CREATE TABLE t1XML ( id INTEGER, f1 INTEGER )
CREATE TABLE t2XML ( ptr INTEGER, f1 INTEGER )
 
INSERT INTO t1XML VALUES (1, 1)
INSERT INTO t1XML VALUES (2, 2)
 
INSERT INTO t2XML VALUES (1, 1)
INSERT INTO t2XML VALUES (1, 4)
INSERT INTO t2XML VALUES (2, 3)
 
**Example AUTO**
 
SELECT * 
FROM t1XML INNER JOIN t2XML ON id = ptr 
FOR XML AUTO

<t1XML id="1" f1="1">
    <t2XML ptr="1" f1="1" />
    <t2XML ptr="1" f1="4" />
</t1XML>
<t1XML id="2" f1="2">
    <t2XML ptr="2" f1="3" />
</t1XML>

Example AUTO + ELEMENT

SELECT * 
FROM t1XML INNER JOIN t2XML ON id = ptr 
FOR XML AUTO ELEMENT

<t1XML>
    <id>1</id>
    <f1>1</f1>
    <t2XML>
        <ptr>1</ptr>
        <f1>1</f1>
    </t2XML>
    <t2XML>
        <ptr>1</ptr>
        <f1>4</f1>
    </t2XML>
</t1XML>
<t1XML>
    <id>2</id>
    <f1>2</f1>
    <t2XML>
        <ptr>2</ptr>
        <f1>3</f1>
    </t2XML>
</t1XML>

Example RAW

SELECT t1XML.*, t2XML.ptr, t2XML.f1 AS 'f11' 
FROM t1XML INNER JOIN t2XML ON id = ptr 
FOR XML RAW

<ROW id="1" f1="1" ptr="1" f11="1" />
<ROW id="1" f1="1" ptr="1" f11="4" />
<ROW id="2" f1="2" ptr="2" f11="3" />