Setting a Variable = Select for XML

[790213]

Hi There,

I'm busy with some SQL XML. It's quite fun but I ran into a bit of snag. I need to set a variable = to a select for xml statement. If I had to put it into code I'd say it has to look something like this:

DECLARE @Str VARCHAR (8000)
SET @Str = (SELECT * FROM tPerson FOR XML AUTO)

As you can imagine this doesn't work. Is there any other way of doing this?

 

[Rhys666]

Remembre the basics of what you're trying to do. You're giving a varchar type variable a value, so the value must therefore be of a varchar type or it won't parse. If you delimit your SQL statement the same as you would when inserting text from a form into a field in the database it should work fine. See below.

DECLARE @Str VARCHAR (8000)
SET @Str = ('SELECT * FROM tPerson FOR XML AUTO')

Rhys
Buffy: Spike, what are you doing here, five words or less!
Spike: Out for a walk... bitch!

 

[790213]

I'm not sure you quite understand what I'm trying to do. I want to set the variable equal to the actual xml coming back in the result set hence the select statement in the brackets.

Do you know how to do this? Please tell me you do

 

[Rhys666]

Hmmm. I know the FOR XML AUTO clause is invalid in ANY sub-selects statements. Why do you need to set the variable to the return value of a FOR XML AUTO statement, it just seems a little strange as this suggests you want to manipulate the varchar value in the variable, and I can't see why you'd want to.
The FOR XML AUTO statement is still very limited in scope basically, I'll paste the BOL section below which you'll probably want to look at, hope it helps.

[BOL]
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™ 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=&quot;Buchanan&quot;/>

When ServerName is a network server, the query returns:

<ServerName.Northwind.dbo.Employees LastName=&quot;Buchanan&quot;/>

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 =&quot;Buchanan&quot;/>

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,
rderID,
rderDate,
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
rderID = p.OrderID
FOR XML AUTO

This is the partial result:

<c CompanyName=&quot;Vins et alcools Chevalier&quot;>
<o OrderID=&quot;10248&quot; OrderDate=&quot;1996-07-04T00:00:00&quot;>
<pr ProductName=&quot;Queso Cabrales&quot;>
<od Quantity=&quot;12&quot; UnitPrice=&quot;14.0000&quot; total=&quot;168.0000&quot;/>
</pr>
<pr ProductName=&quot;Singaporean Hokkien Fried Mee&quot;>
<od Quantity=&quot;10&quot; UnitPrice=&quot;9.8000&quot; total=&quot;98.0000&quot;/>
</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,
rderID,
rderDate,
p.ProductName,
p.Quantity,
p.UnitPrice,
p.total
FROM Customers AS c
JOIN
Orders AS o
ON
c.CustomerID = o.CustomerID
JOIN
p
ON
rderID = p.OrderID
FOR XML AUTO

This is the partial result:

<c CompanyName=&quot;Vins et alcools Chevalier&quot;>
<o OrderID=&quot;10248&quot; OrderDate=&quot;1996-07-04T00:00:00&quot;>
<p ProductName=&quot;Queso Cabrales&quot;
Quantity=&quot;12&quot;
UnitPrice=&quot;14.0000&quot;
total=&quot;168.0000&quot;/>
</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=&quot;namespace-urn&quot; namespace:a=&quot;1&quot;/>

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=&quot;1&quot; Col2=&quot;dbobject/Computed[@Col1='1']/@Col2&quot;/>

©1988-2004 Microsoft Corporation. All Rights Reserved.
[/BOL]

Rhys
Buffy: Spike, what are you doing here, five words or less!
Spike: Out for a walk... bitch!

 

[790213]

I want to set the variable equal to the select statement so I can pass it through to annother stored procedure that invokes a COM object. Do you know of any way I can do that or any alternatives I can use in this regard?