The following query returns two columns with the same name, and three columns with no name:
select t1.title_id, t2.title_id, t2.advance-t1.advance, t1.price*t1.total_sales, t2.price*t2.total_sales from pubs2..titles t1, pubs2..titles t2 where t1.price=t2.price and t2.advance-t1.advance>3000 title_id title_id ------ ------ -------- --------- --------- BU2075 MC3021 4,875.00 55,978.78 66,515.54 MC2222 BU1032 5,000.00 40,619.68 81,859.05 MC2222 BU7832 5,000.00 40,619.68 81,859.05
When this data is mapped to XML, the columns become elements or attributes (depending on the columnstyle option), and such elements and attributes must have unique names. The generated XML therefore adds integer suffixes to duplicate column names, and generates unique suffixed names for unnamed columns. For example (using the above query):
select t1.title_id, t2.title_id, t2.advance-t1.advance, t1.price*t1.total_sales, t2.price*t2.total_sales from pubs2..titles t1, pubs2..titles t2 where t1.price=t2.price and t2.advance-t1.advance>3000 for xml ---------------------------------------------------- <resultset xmlns:xsi="http://www.w3.org/2001 /XMLSchema-instance"> <row <title_id1>BU2075</title_id1> <title_id2>MC3021</title_id2> <C1>4875.00</C1> <C2>55978.78</C2> <C3>66515.54</C3> </row> <row> <title_id1>MC2222</title_id1> <title_id2>BU1032</title_id2> <C1>5000.00</C1> <C2>40619.68</C2> <C3>81859.05</C3> </row> <row> <title_id1>MC2222</title_id1> <title_id2>BU7832</title_id2> <C1>5000.00</C1> <C2>40619.68</C2> <C3>81859.05</C3> </row> </resultset>
If the name XML generates for an unnamed column corresponds to an existing column name, that generated name is skipped. In the following example, the last of the unnamed columns has the explicit column name “C1”, so “C1” is not used as a generated column name:
select t1.title_id, t2.title_id, t2.advance-t1.advance, t1.price*t1.total_sales,t2.price*t2.total_sales as C1 from pubs2..titles t1, pubs2..titles t2 where t1.price=t2.price and t2.advance-t1.advance>3000 for xml ----------------------------------------------------- <resultset xmlns:xsi="http://www.w3.org/2001 /XMLSchema-instance"> <row> <title_id1>BU2075</title_id1> <title_id2>MC3021</title_id2> <C2>4875.00</C2> <C3>55978.78</C3> <C1>66515.54</C1> </row> <row> <title_id1>MC2222</title_id1> <title_id2>BU1032</title_id2> <C2>5000.00</C2> <C3>40619.68</C3> <C1>81859.05</C1> </row> <row> <title_id1>MC2222</title_id1> <title_id2>BU7832</title_id2> <C2>5000.00</C2> <C3>40619.68</C3> <C1>81859.05</C1> </row> </resultset>
In the previous examples, the names generated for unnamed columns have the form “C1”, “C2”, and so on. These names consist of the base name “C” and an integer suffix. You can specify an alternative base name with the prefix option.
This example shows prefix=’column_’:
select t1.title_id, t2.title_id, t2.advance-t1.advance, t1.price*t1.total_sales, t2.price*t2.total_sales from pubs2..titles t1, pubs2..titles t2 where t1.price=t2.price and t2.advance-t1.advance>3000 for xml option "prefix=column_" --------------------------------------- <resultset xmlns:xsi="http://www.w3.org/2001 /XMLSchema-instance"> <row> <title_id1>BU2075</title_id1> <title_id2>MC3021</title_id2> <column_1>4875.00</column_1> <column_2>55978.78</column_2> <column_3>66515.54</column_3> </row> <row> <title_id1>MC2222</title_id1> <title_id2>BU1032</title_id2> <column_1>5000.00</column_1> <column_2>40619.68</column_2> <column_3>81859.05</column_3> </row> <row> <title_id1>MC2222</title_id1> <title_id2>BU7832</title_id2> <column_1>5000.00</column_1> <column_2>40619.68</column_2> <column_3>81859.05</column_3> </row> </resultset>
