Details of "FOR XML" sql clause in SQL-Server

SQL Server allows developers to query SQL Server and receive data in XML format via a special clause: FOR XML. This clause provides 4 different modes for the XML format returned: RAW, AUTO, EXPLICIT and PATH (new feature of SQL Server 2005)
 
RAW:
It generates single elements, which are named row, for each row returned.
Sample:
select id, name from customer where id = 3 for xml raw
<rowid="3"name="Customer         3" />
 
AUTO:
            Looks quite like “RAW”, the difference is the element is named as the table name, instead of “row”.
            Sample:
            select id, name from customer where id = 3 for xml auto
      <customerid="3"name="Customer         3" />
 
Question here:
            How to get the record set in xml which contains more than one record? In other words, how to add a root node?
            Sample:
            select id, name from customer where id in(3,4) for xml auto
            <customerid="3"name="Customer         3" />
<customerid="4"name="Customer         4" />
            It seems we have to use EXPLICIT or PATH to meet the requirement.
           
EXPLICIT:
            Very powerful, could generate xml data with several levels of nodes.
            The sql sentence must have tag, parent, and <nodeName>!<tagValue>[!<attributeName>] in the select clause. If the ‘!<attribute>’ is included in the column name, then this column will be shown as attribute with the <attributeName>; otherwise, it will be shown as element
---------------- add root node ---------------
select 1 as tag, null as parent, null as [root!1], null as [data!2!id], null as [data!2!name]
union
select 2, 1, null,id, name from customer where id in(3,4)
for xml explicit
            <root>
 <dataid="3"name="Customer         3" />
 <dataid="4"name="Customer         4" />
</root>
 
---------------- 3 levels xml ---------------
SELECT   1                  as Tag,
         NULL               as Parent,
         NULL               as [root!1],
         NULL               as [C!2!customerid],
         NULL               as [C!2!customerName],
         NULL               as [O!3!orderid],
         NULL               as [O!3!orderName]
UNION all
SELECT 2, 1, NULL, id, name, NULL, NULL
FROM Customer as C where c.id in(3,4)
UNION all
SELECT 3, 2, NULL, C.id, C.name, O.id, O.name
    FROM Customer C, Orders O
    WHERE (C.id = O.uid) and c.id in(3,4)
order by [C!2!customerid],[O!3!orderid]
FOR XML EXPLICIT
 
<root>
 <Ccustomerid="3"customerName="Customer         3">
    <Oorderid="3"orderName="Order         3" />
    <Oorderid="33"orderName="Order        33" />   
 </C>
 <Ccustomerid="4"customerName="Customer         4">
    <Oorderid="4"orderName="Order         4" />
    <Oorderid="34"orderName="Order        34" />
 </C>
</root>
PATH (2005 new feature):
            Much easier to use than EXPLICIT.
---------------- add root node ---------------
select id , name from customer where id in(3,4)
for xml path, root('root')
<root>
 <row>
    <id>3</id>
    <name>Customer         3</name>
 </row>
 <row>
    <id>4</id>
    <name>Customer         4</name>
 </row>
</root>
 
select id as '@id', name as '@name' from customer where id in(3,4)
for xml path('data'), root('root')
<root>
 <dataid="3"name="Customer         3" />
 <dataid="4"name="Customer         4" />
</root>
---------------- 3 levels xml ---------------
SELECT id as "@CustomerID",
           (SELECT id as "@OrderID"
            FROM Orders
            WHERE Orders.uid = Customer.id
            FOR XML PATH('Orders'), TYPE)
FROM Customer where id in(3,4)
FOR XML PATH('Customer'), root('myRoot')
<myRoot>
 <CustomerCustomerID="3">
    <OrdersOrderID="3" />
    <OrdersOrderID="33" />   
 </Customer>
 <CustomerCustomerID="4">
    <OrdersOrderID="4" />
    <OrdersOrderID="34" />   
 </Customer>
</myRoot>
 
Elements or Attributes:
1)      For RAW, AUTO and EXPLICIT, fields are shown as attributes by default, unless use the Elements options.
select id, name from customer where id = 3 for xml auto
      <customerid="3"name="Customer         3" />
 
select * from customer where id = 3 for xml auto, elements
<customer>
 <id>3</id>
 <name>Customer         3</name>
</customer>
2)      For PATH, fields are shown as elements by default, unless use fieldname as ‘@attributeName’ in the select clause.
See the topic PATH for examples.
 
 
展开阅读全文

没有更多推荐了,返回首页