Peter, I just tested this with DBD::ODBC. I can get XML back from the server, but it comes in one large result set (1 wide column). I'm not entirely sure that's what you'd want to do, but you can see for yourself.
Attached is my testxml.pl script which just asks for the XML from the server and gets it back. I would think that getting one big result set, then having to parse it, may be problematic, unless, of course, you don't have to parse it... However, it would be very easy to generate the XML that this is generating, which may be less memory intensive for you (and may not). Your mileage may vary. Regards, Jeff > -----Original Message----- > From: Levine, Peter W [mailto:[EMAIL PROTECTED]] > Sent: Monday, August 26, 2002 2:34 PM > To: 'Jeff Urlwin' > Subject: RE: XML & DBD:ODBC > > > Jeff, > > MS Server 2000 can return data as XML or as rowsets. The main > interface for > this is MS IIS which can be set up to query MS Server 2000 by URL but I'm > investigating what can be done without IIS. > > I got an email (attached) from Christopher Pryce who has started work on a > perl modile interface (MSX.pm) > ------------------------------------------------------ > > Peter: > > If you'd like, I've attached an alpha version of a module that I > am working > on automate the XML interface to MS SQL. > > I log on as root, and save the module in > /usr/local/lib/perl5/site_perl/5.6.1/MSX.pm > > > Your path to perl may vary. If you don't have root access, install it any > where, make sure it is world readable (0644) and make sure that you add: > > use lib 'path/to/directory_where_MSX.pm_lives/'; > > > You can run perldoc MSX (or perldoc /path/to/MSX.pm) to view the barebones > documentation. It may get you started. There is precious little error > checking. I'm still working on improving that. > > Any feedback good or bad is appreciated. > > Christopher Pryce > -- > > "There are only 10 kinds of people in the world-- > Those who understand binary, and those who don't." [ rec.humor.funny > ___________________________________________________________________ > > I'm also including some info about using XML in MS Server 2000: > > ---------------------------- > Guidelines for Using the FOR XML Clause > The FOR XML clause is valid only in the SELECT statement and is subject to > these limitations: > > FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or > DELETE statements, a nested SELECT statement, or other statements (SELECT > INTO, assignment). For example, subselects as shown in these examples are > not supported: > Example A > > SELECT * > FROM Table1 > WHERE ......(SELECT * FROM Table2 FOR XML RAW) > > Example B > > DECLARE @doc nchar(3000) > SET @doc = (SELECT * FROM Customers WHERE CustomerID = 'ALFKI' > FOR XML RAW) > > FOR XML is not valid for any selection that is used with a > COMPUTE BY or FOR > BROWSE clause, for example: > SELECT OrderID, UnitPrice > FROM [Order Details] > ORDER BY OrderID COMPUTE SUM(UnitPrice) BY OrderID > > GROUP BY and aggregate functions are currently not supported with FOR XML > AUTO. For example: > SELECT max(price), min(price), avg(price) > FROM titles > FOR XML AUTO > > FOR XML is not valid in a SELECT statement used in a view > definition or in a > user-defined function that returns a rowset. For example, this > statement is > not allowed: > CREATE VIEW AllOrders AS SELECT * FROM Orders FOR XML AUTO > > However, a statement such as the following is allowed: > > SELECT * FROM ViewName FOR XML AUTO are allowed. > > FOR XML cannot be used in a selection that requires further > processing in a > stored procedure. > > > FOR XML cannot be used with cursors. > > > Generally, FOR XML cannot be used for any selections that do not produce > direct output to the Microsoft� SQL Server(tm) 2000 client. > > > FOR XML cannot be used in a stored procedure when called in an INSERT > statement. > > > When a SELECT statement with a FOR XML clause specifies a > four-part name in > the query, the server name is not returned in the resulting XML document > when the query is executed on the local computer. However, the server name > is returned as the four-part name when the query is executed on a network > server. > For example, consider this query: > > SELECT TOP 1 LastName > FROM ServerName.Northwind.dbo.Employees > FOR XML AUTO > > When ServerName is a local server, the query returns: > > <Northwind.dbo.Employees LastName="Buchanan"/> > > When ServerName is a network server, the query returns: > > <ServerName.Northwind.dbo.Employees LastName="Buchanan"/> > > This can be avoided by specifying this alias: > > SELECT TOP 1 LastName > FROM ServerName.Northwind.dbo.Employees x > FOR XML AUTO > > This query returns: > > <x ="Buchanan"/> > > Using derived tables in a SELECT statement with FOR XML AUTO may > not produce > the nesting you want. > The FOR BROWSE mode is implemented when a query with the FOR XML AUTO mode > is specified. The FOR XML AUTO mode uses the information provided > by the FOR > BROWSE mode in determining the hierarchy in the result set. > > For example, consider the following query. A derived table P is created in > the query. > > SELECT c.CompanyName, > o.OrderID, > o.OrderDate, > p.ProductName, > p.Quantity, > p.UnitPrice, > p.Total > FROM Customers AS c > JOIN > Orders AS o > ON > c.CustomerID = o.CustomerID > JOIN > ( > SELECT od.OrderID, > pr.ProductName, > od.Quantity, > od.UnitPrice, > od.Quantity * od.UnitPrice AS total > FROM Products AS pr > JOIN > [Order Details] AS od > ON > pr.ProductID = od.ProductID > ) AS p > ON > o.OrderID = p.OrderID > FOR XML AUTO > > This is the partial result: > > <c CompanyName="Vins et alcools Chevalier"> > <o OrderID="10248" OrderDate="1996-07-04T00:00:00"> > <pr ProductName="Queso Cabrales"> > <od Quantity="12" UnitPrice="14.0000" total="168.0000"/> > </pr> > <pr ProductName="Singaporean Hokkien Fried Mee"> > <od Quantity="10" UnitPrice="9.8000" total="98.0000"/> > </pr> > </c> > > In the resulting XML document, the <p> element is missing, and > the <pr> and > <od> elements are returned. This occurs because the query optimizer > eliminates the P table in the result and returns a result set > consisting of > the od and pr tables. > > This can be avoided by rewriting the query. For example, you can > rewrite the > query is to create a view and use it in the SELECT statement: > > CREATE VIEW p AS > SELECT od.OrderID, > pr.ProductName, > od.Quantity, > od.UnitPrice, > od.Quantity * od.UnitPrice AS total > FROM Products AS pr > JOIN > [Order Details] AS od > ON > pr.ProductID = od.ProductID > > And then write the SELECT statement: > > SELECT c.CompanyName, > o.OrderID, > o.OrderDate, > p.ProductName, > p.Quantity, > p.UnitPrice, > p.total > FROM Customers AS c > JOIN > Orders AS o > ON > c.CustomerID = o.CustomerID > JOIN > p > ON > o.OrderID = p.OrderID > FOR XML AUTO > > This is the partial result: > > <c CompanyName="Vins et alcools Chevalier"> > <o OrderID="10248" OrderDate="1996-07-04T00:00:00"> > <p ProductName="Queso Cabrales" > Quantity="12" > UnitPrice="14.0000" > total="168.0000"/> > </o> > </c> > > In addition, SQL Server names containing characters that are > invalid in XML > names (such as spaces) are translated into XML names in a way in which the > invalid characters are translated into escaped numeric entity encoding. > > There are only two nonalphabetic characters that can begin an XML > name: the > colon (:) and the underscore (_). Because the colon (:) is > already reserved > for namespaces, the underscore (_) is chosen as the escape character. The > escape rules used for encoding are: > > Any UCS-2 character that is not a valid XML name character > (according to the > XML 1.0 specification) is escaped as _xHHHH_, where HHHH stands for the > four-digit hexadecimal UCS-2 code for the character in the most > significant > bit-first order. For example, the table name Order Details is encoded as > Order_x0020_Details. > > > Characters that do not fit into the UCS-2 realm (the UCS-4 > additions of the > range U+00010000 to U+0010FFFF) are encoded as _xHHHHHHHH_, where HHHHHHHH > stands for the eight-digit hexadecimal UCS-4 encoding of the character. > > > The underscore character does not need to be escaped unless it is followed > by the character x. For example, the table name Order_Details is not > encoded. > > > The colon (:) in identifiers is not escaped so that the namespace element > and attribute names can be generated by the FOR XML query. For > example, the > following query generates a namespace attribute with a colon in the name: > SELECT 'namespace-urn' as 'xmlns:namespace', > 1 as 'namespace:a' > FOR XML RAW > > The query produces this result: > > <row xmlns:namespace="namespace-urn" namespace:a="1"/> > > In a SELECT query, casting of any column to a binary large object (BLOB) > makes it a temporary entity (losing its associated table name and column > name). This causes AUTO mode queries to generate an error because it does > not know where to place this value in the XML hierarchy, for example: > CREATE TABLE MyTable (Col1 int PRIMARY KEY, Col2 binary) > INSERT INTO MyTable VALUES (1, 0x7) > > This query produces an error because of the casting to a BLOB: > > SELECT Col1, > CAST(Col2 as image) as Col2 > FROM MyTable > FOR XML AUTO > > If you remove the casting, the query produces results as expected: > > SELECT Col1, > Col2 > FROM MyTable > FOR XML AUTO > > This is the result: > > <Computed Col1="1" Col2="dbobject/Computed[@Col1='1']/@Col2"/> > > > See Also > > Executing SQL Statements Using HTTP > > Executing Template Files Using HTTP > > SELECT > > �1988-2000 Microsoft Corporation. All Rights Reserved. > > Here's a relevant passge froMS Server 2000 Help > > Retrieving and Writing XML Data > You can execute SQL queries to return results as XML rather than standard > rowsets. These queries can be executed directly or from within stored > procedures. To retrieve results directly, you use the FOR XML > clause of the > SELECT statement, and within the FOR XML clause you specify an XML mode: > RAW, AUTO, or EXPLICIT. > > For example, this SELECT statement retrieves information from > Customers and > Orders table in the Northwind database. This query specifies the AUTO mode > in the FOR XML clause: > > SELECT Customers.CustomerID, ContactName, CompanyName, > Orders.CustomerID, OrderDate > FROM Customers, Orders > WHERE Customers.CustomerID = Orders.CustomerID > AND (Customers.CustomerID = N'ALFKI' > OR Customers.CustomerID = N'XYZAA') > ORDER BY Customers.CustomerID > FOR XML AUTO > > > > -----Original Message----- > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > Sent: Monday, August 26, 2002 10:29 AM > To: Levine, Peter W; [EMAIL PROTECTED] > Subject: RE: XML & DBD:ODBC > > > Peter, > > I'm not sure what you mean. Can you give me an example? > > Thanks, > > Jeff > > > > > > > Hi, > > > > Can the DBD-ODBC module make use of of MS SQL Server's FOR SQL > clause? I'm > > evaluating the pros & cons of getting my data back from MS SQL > > Server in XML > > format. (BTW I'm use Apache webserver on Solaris.) > > > > Pete > > > > Peter Levine > > [EMAIL PROTECTED] > > (415) 286-5716 > > > > > > > > > > >
testxml.pl
Description: Binary data
