CODEWITHSUNDEEP
sqlxml
Darrel Miller
Darrel Miller

Reputation: 142014

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

I am currently using Microsoft's FOR XML PATH feature to pull data back from MS SQL Server in an XML format. I really like the feature but am always uncomfortable about using vendor specific functions.

Do any of the other major database vendors have something similar?

Edit

Let me be more specific with type of 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 generate 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 on a column by column basis. I can create attributes of elements to group related columns together. I can use sub-queries to generate nested elements. You can even use functions that return XML to generate trees that are arbitrary depth.

Upvotes: 7

Views: 11600

Answers (3)

Martlark
Martlark

Reputation: 14581

Oracle has a whole package for XML handling in it's databases. You can even store XML straight into the database (no INSERTS ) and query it. See: How to format xml with oracle for some examples.

Here is one of the examples:

    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>

Upvotes: 1

kquinn
kquinn

Reputation: 10740

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

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300489

FOR XML PATH is not in the ANSI SQL-92 standard, so it will be up to the individual vendors. I do not know of any equivalent in Oracle or MySQL.

Upvotes: 2

Related Questions