|
How To: Shape FOR XML AUTO results using SQL views (46318 Requests)
Sometimes it is easier to use FOR XML AUTO to retrieve XML from SQL Server than it is to use FOR XML EXPLICIT. However, if you're combining data from multiple tables you may not get results you expect using the AUTO mode.
Suppose that we want to get a list of the products in our database, but we also want to include the supplier name and category name. Below is an example template that you can run against the Northwind database.
<products>
<sql:query xmlns:sql="urn:schemas-microsoft-com:xml-sql">
select ProductID,
ProductName,
CompanyName,
CategoryName
from Products, Suppliers, Categories
where Products.SupplierID = Suppliers.SupplierID
and Products.CategoryID = Categories.CategoryID
for xml auto
</sql:query>
</products>
If you run the above template (or just run the select statement in query analyzer) you will get the right results, but the format leaves something to be desired. Below is a snippet of what the output will look like.
<products>
<Products ProductID="1" ProductName="Modified product">
<Suppliers CompanyName="Exotic Liquids">
<Categories CategoryName="Beverages"/>
</Suppliers>
</Products>
<Products ProductID="2" ProductName="Chang">
<Suppliers CompanyName="Exotic Liquids">
<Categories CategoryName="Beverages"/>
</Suppliers>
</Products>
...
</products>
All the data is there, but it isn't very readable and we are creating a lot of data bloat by adding the extra tags. We could use a FOR XML EXPLICIT query instead of the FOR XML AUTO or we could just use a SQL view. Below is the SQL statement to create our view. We just use the select statement with the FOR XML AUTO and create a view from it.
create view vw_ProductInfo
as
select ProductID,
ProductName,
CompanyName,
CategoryName
from Products, Suppliers, Categories
where Products.SupplierID = Suppliers.SupplierID
and Products.CategoryID = Categories.CategoryID
Once we have created our view we can modify our template. Now we will just select * from the view and add the FOR XML AUTO statement to it.
<products>
<sql:query xmlns:sql="urn:schemas-microsoft-com:xml-sql">
select *
from vw_ProductInfo as product
for xml auto </sql:query>
</products>
The "as product" will shape the resulting XML further since the elements will be named product instead of vw_ProductInfo. The resulting XML will be as follows.
<products>
<product ProductID="1" ProductName="Modified product" CompanyName="Exotic Liquids" CategoryName="Beverages"/>
<product ProductID="2" ProductName="Chang" CompanyName="Exotic Liquids" CategoryName="Beverages"/>
...
</products>
So by using a SQL view the results of a FOR XML AUTO query can be shaped to the way we want it to look.
Feedback
# re: How To: Shape FOR XML AUTO results using SQL views
12/6/2003 5:10 AM
Danny
I have recently come across this exact problem myself. Is the only solution to use a view? If not, do you know of an example where I can see a solution without resorting to view?
# re: How To: Shape FOR XML AUTO results using SQL views
12/8/2003 8:29 AM
Bryant
Well you can also use an XSL to transform the XML, but a SQL View might be a simplier approach.
# re: How To: Shape FOR XML AUTO results using SQL views
1/5/2004 11:41 AM
Paul
It seems to me that using a sql view has the same effect as using for xml raw.
# re: How To: Shape FOR XML AUTO results using SQL views
3/17/2004 10:30 AM
Mark Brown
Personally, I prefer to do all of my shaping through sub-queries.
The following query will produce the exact same results as the view-based version above, but without the overhead of a View for every XML query.
select *
from (
select
ProductID,
ProductName,
CompanyName,
CategoryName
from Products, Suppliers, Categories
where Products.SupplierID = Suppliers.SupplierID
and Products.CategoryID = Categories.CategoryID
) products
for xml auto
# re: How To: Shape FOR XML AUTO results using SQL views
7/31/2004 6:54 AM
max
There is another way
select ProductID,
ProductName,
ISNULL(CompanyName, '') CompanyName,
ISNULL(CategoryName, '') CategoryName
from Products, Suppliers, Categories
where Products.SupplierID = Suppliers.SupplierID
and Products.CategoryID = Categories.CategoryID
for xml auto
# re: How To: Shape FOR XML AUTO results using SQL views
12/7/2004 8:47 AM
howard
huh?
# re: How To: Shape FOR XML AUTO results using SQL views
3/22/2005 10:48 AM
Craig Bovis
Views seem to be the nicest way of shaping data like this as they can quickly be built up using the wizard.
Drag and drop what fields you need and relationships are done for you if you have previous relationship diagrams already made in the database (like you should).
The problem I seem to find with views is when joining them in a query I cannot get nested results back but instead receive 1 level XML similar to what the recordset output would be.
Can anyone shed any light on this?
Just found the site btw, looks like it could be a useful resource for me in the future!
# re: How To: Shape FOR XML AUTO results using SQL views
8/3/2005 4:37 AM
Gal
For XML heirch
I have three tables in MS-SQL let say
T1
ID int
Name char(32)
T2
ID int
prop21
prop22
T3
ID int
prop21
prop22
and I want to run select for xml
where the result should be like
<T1 Name="" ID="">
<T2 prop21="" prop22=""></T2>
<T3 prop31="" prop32=""></T3>
</T1>
Can and How I can do that
# re: How To: Shape FOR XML AUTO results using SQL views
8/3/2005 4:41 AM
Gal
For XML heirch
I have three tables in MS-SQL let say
T1
ID int
Name char(32)
T2
ID int
prop21
prop22
T3
ID int
prop21
prop22
and I want to run select for xml
where the result should be like
<T1 Name="" ID="">
<T2 prop21="" prop22=""></T2>
<T3 prop31="" prop32=""></T3>
</T1>
Can and How I can do that
# re: How To: Shape FOR XML AUTO results using SQL views
8/10/2005 9:24 AM
Jeremy Ragsdale
Can I use FOR XML AUTO if I am using OleDB in my App? I would like to return XML from a stored procedure using C#, in my code I have to use an OleDB type command. Can this be done?
How?
# re: How To: Shape FOR XML AUTO results using SQL views
2/24/2006 6:25 PM
ooo
ooo
# re: How To: Shape FOR XML AUTO results using SQL views
4/28/2006 11:41 PM
Raj
I am using FOR XML AUTO query in Java(Jsp).
If i ResultSet to get the results its giving error that FOR XML clause is not in Cursor.
How can get the results?
# re: How To: Shape FOR XML AUTO results using SQL views
4/28/2006 11:42 PM
Raj
I am using FOR XML AUTO query in Java(Jsp).
If i ResultSet to get the results its giving error that FOR XML clause is not in Cursor.
How can get the results?
yfijkd ntodf motq uqxhb tiuomp hvxkgn iecz
pdvti gdqbh kgjquzrs tsgu lemrajpz ferlvzh ptvh
# re: How To: Shape FOR XML AUTO results using SQL views
3/18/2008 2:26 PM
arthur tom
when I try to save a view about "for xml auto" , it doesn't save and giving erros message that "create view or function failed because no columname was specificied for column1"
how can I do about that?
# re: How To: Shape FOR XML AUTO results using SQL views
5/12/2008 5:35 AM
drilldown
More books and pubs?? Do any of you work in the real world where there are more complex relaitonal model than this!?!
Duplicates? Nested Nodes? This is as just as useful as 200 other posts on FOR XML... ie, not very useful at all.
# re: How To: Shape FOR XML AUTO results using SQL views
5/23/2008 12:14 PM
damashiuchi
@Jeremy Ragsdale and raj
you have to use a stream object to read the results from an ado "for xml" query - check on msdn - you should be able to find info
@arthur tom
you can't create a view with the "for xml" clause - you can QUERY a view with "for xml" ... but the view has to return a standard tablular result on its own
@drilldown
i find that i can do just about anything i need to do in the real world with the "for xml auto" clause ... you just have to be careful and deliberate with your joins ... you can use the "for xml explicit" clause to build parent/child relationships however you want, but the queries can get exceedingly (and often unnecessarily) complex ... the ONLY thing you can't do with auto that you can do with explicit is return results as element data instead of an attribute in the xml results ... that's it. as long as you have good logical data structures and build your select queries carefully, you can do anything and everything with "for xml auto"
just think of each table in your query as an element node and each column as an attribute of that node, and your join conditions in the from clause define the parent/child relationships between nodes (tables) -- just like in the example above, "Products" is a parent of "Suppliers" which is a parent of "Catergories", so every time "Products.SupplierID" == "Suppliers.SupplierID", you get another "Supplier" child node ...
# re: How To: Shape FOR XML AUTO results using SQL views
10/12/2008 7:56 AM
mark_wills
@arthur tom said can't create a view, well, you can by wrapping your "select for xml" in another select statement... ie in sql2005 :
select (
select * from mytable for xml auto, type, elements
) as myxml
then wrap that in a view ie:
create view vw_myxml
as
select
(
select * from mytable for xml auto, type, elements
) as myxml
;
# re: How To: Shape FOR XML AUTO results using SQL views
10/17/2008 8:13 AM
Gariele
Hi,
i use joins to to define parent/child relations, but i get a single empty child for parents with no children..
Is there a way to not return the empty child for a parent with no children ?
regards
|