Saturday, August 25, 2007

SQL SERVER : Selecting data from temporary tables & table variables using FOR XML

We can
convert the relational data stored in the table into XML by using the FOR XML
syntax, I am not going to the explain all the features of FOR XML.



Physical table


SELECT * FROM ASPNET_Roles FOR XML AUTO,ROOT ('A'),ELEMENTS

this select statement will give the following output

<A><URR_ASPNET_Roles>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>89C306A6-2D21-42AB-A42F-5029091CB4E8</RoleId>
<RoleName>SBU
User</RoleName>
<LoweredRoleName>sbu
user</LoweredRoleName>
<USER_TYPE>2</USER_TYPE>
</URR_ASPNET_Roles>
<URR_ASPNET_Roles>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>180BA05D-D2A5-49CA-BED3-3C6F080F6543</RoleId>
<RoleName>Super
User</RoleName>
<LoweredRoleName>super
user</LoweredRoleName>
<USER_TYPE>1</USER_TYPE>
</URR_ASPNET_Roles>
</A>


its is a perfect xml with all the nodes and 'A' as the root element

now consider selecting the data from temporary table & table variable


SELECT *
INTO #TBL_TMP FROM
ASPNET_Roles

the values of the table URR_ASPNET_Roles is inserted into temporary table #TBL_TMP

SELECT *
FROM #TBL_TMP FOR
XML AUTO,ROOT('A'),ELEMENTS

the above select statement will produce the following output

<A>
<_x0023_TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>89C306A6-2D21-42AB-A42F-5029091CB4E8</RoleId>
<RoleName>SBU
User</RoleName>
<LoweredRoleName>sbu
user</LoweredRoleName>
<USER_TYPE>2</USER_TYPE>
</_x0023_TBL_TMP>
<_x0023_TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>180BA05D-D2A5-49CA-BED3-3C6F080F6543</RoleId>
<RoleName>Super
User</RoleName>
<LoweredRoleName>super
user</LoweredRoleName>
<USER_TYPE>1</USER_TYPE>
</_x0023_TBL_TMP>
</A>


but what is _x0023_
doing in output, from where did it originate ? how can we remove this
addition in the xml tag

consider the following statement

SELECT *
FROM #TBL_TMP TBL_TMP
FOR
XML AUTO,ROOT('A'),ELEMENTS

this will produce the following output

<A>
<TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>89C306A6-2D21-42AB-A42F-5029091CB4E8</RoleId>
<RoleName>SBU
User</RoleName>
<LoweredRoleName>sbu
user</LoweredRoleName>
<USER_TYPE>2</USER_TYPE>
</TBL_TMP>
<TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>180BA05D-D2A5-49CA-BED3-3C6F080F6543</RoleId>
<RoleName>Super
User</RoleName>
<LoweredRoleName>super
user</LoweredRoleName>
<USER_TYPE>1</USER_TYPE>
</TBL_TMP>
</A>

the output is clean with no extra additions !!!

How ? in the second statement we have provided an alias for the table which is
displayed in the output

same is the case with table variable's

DECLARE @tblVar TABLE
(ID INT,DATA
VARCHAR(10))

INSERT INTO @tblVar
VALUES (1,'DATA1')

SELECT *
FROM @tblVar FOR
XML AUTO,ROOT('A'),ELEMENTS

<A>
<
_x0040_tblVar>
<ID>1</ID>
<DATA>DATA1</DATA>
</
_x0040_tblVar>
</A>

SELECT *
FROM @tblVar TBLVAR FOR
XML AUTO,ROOT('A'),ELEMENTS

<A>
<TBLVAR>
<ID>1</ID>
<DATA>DATA1</DATA>
</TBLVAR>
</A>


why are such additional unwanted strings displayed in temporary tables and table
variables and not in the case of physical tables

temporary tables and table variables have # & @ attached to their names
respectively, so these special characters are parsed by SQL server and converted
into xml, in both cases we can remove these unwanted additions by using aliases.


Happy programming

Post a Comment