Is there an equivalent MS SQL "FOR XML PATH" in other database products?

Question

I am currently using the Microsoft FOR XML PATH function to output data from MS SQL Server in XML format. I really like this feature, but it is always inconvenient for me to use vendor specific functions.

Does any other major database provider have something similar?

Edit

Let me clarify what features I'm looking for

In MS SQL you can use this query

SELECT so.id AS '@Id', so.Code AS '@Code', cu.Code AS 'Customer/@Code', cu.Name AS 'Customer/@Name', (SELECT Item_Num AS '@Item', pa.Code AS '@PartCode' FROM tblSalesItem si LEFT JOIN tblPart pa ON pa.Id = si.Part_Id WHERE si.SalesOrder_ID = so.Id FOR XML PATH('SalesItem') , type) FROM tblSalesOrder so JOIN tblCustomer cu ON so.customer_id = cu.ID FOR XML PATH('SalesOrder'), ROOT('SalesOrders') 

to create this xml

 <SalesOrders> <SalesOrder Id="13" Code="C1002 "> <Customer Code="ROBERTS " Name="Roberts Equipment Inc." /> <SalesItem Item="1" PartCode="FP-0001" /> <SalesItem Item="2" PartCode="FP-0003" /> </SalesOrder> <SalesOrder Id="15" Code="C1004 "> <Customer Code="EXBEL-LIFTS" Name="Exbel Lifts Inc." /> <SalesItem Item="1" /> </SalesOrder> </SalesOrders> 

I can control the use of elements and attributes in a column by column. I can create element attributes to group related columns. I can use subqueries to create nested elements. You can even use functions that return XML to generate trees that are arbitrary depth.

Answer 1

FOR XML PATH not available in the ANSI SQL-92 standard, so this is vendor-specific. I do not know any equivalent in Oracle or MySQL.

Answer 2

PostgreSQL 8.3 does not specifically have FOR XML PATH , but its XML functions (see section 9.14 of the manual , especially 9.14.3 ) seem to provide very similar functions.

Answer 3

Oracle has a whole package for processing XML in it. You can even store XML directly in the database (without INSERTS) and query it. See: How to format xml with oracle for some examples.

Here is one example:

  SQL> select dbms_xmlgen.getxml('select employee_id, first_name, 2 last_name, phone_number from employees where rownum < 6') xml 3 from dual <?xml version="1.0"?> <ROWSET> <ROW> <EMPLOYEE_ID>100</EMPLOYEE_ID> <FIRST_NAME>Steven</FIRST_NAME> <LAST_NAME>King</LAST_NAME> <PHONE_NUMBER>515.123.4567</PHONE_NUMBER> </ROW> <ROW> <EMPLOYEE_ID>103</EMPLOYEE_ID> <FIRST_NAME>Alexander</FIRST_NAME> <LAST_NAME>Hunold</LAST_NAME> <PHONE_NUMBER>590.423.4567</PHONE_NUMBER> </ROW> <ROW> <EMPLOYEE_ID>104</EMPLOYEE_ID> <FIRST_NAME>Bruce</FIRST_NAME> <LAST_NAME>Ernst</LAST_NAME> <PHONE_NUMBER>590.423.4568</PHONE_NUMBER> </ROW> </ROWSET>