Skip to main content

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

Labels:

Comments

Post a Comment

Popular posts from this blog

PDCA & SCRUM (or Agile); Why is it important?

The PDCA (Plan DO Check Act) cycle was made popular by Dr. W. Edwards Deming. This is a scientific cyclic process which can be used to improve the process (or product). This is cyclic in nature and usually time boxed. Plan  This is the first stage of the process. During this step the team discusses the objectives, the process and the clear conditions of exit (conditions of acceptance). This stage sets the measurable and achievable goals for the team. DO Team works together to achieve the objective set in the planning phase. Team works with the set of agreed process. Check Once the implantation is done team regroups and verifies the output and compares it to the agreed conditions of acceptance decided during the planning phase. The deviation, if any, is noted down. ACT If any deviation in planned tasks is observed during the Check stage, a root cause analysis is conducted. Team brainstorms and identifies the changes required to prevent such deviatio...
Post a Comment
Read more

Why is potentially shippable product quality important

Agile teams work in iterations. During this period, they are supposed to work on product increments which can be “delivered” at the end of iteration. But how you know that the correct product was delivered? Many teams have different kinds of acceptance criteria and Definition of Done (DoD). But in many cases, this “done” is not the real “done” there might be some testing pending, some integration or review pending or anything else which prevents the actual use of the product increment. Many of these teams will need additional iterations to finish hardening their products. Many teams will implement different types of “gates” or approval steps to move to next stage. The free flow of product will be interrupted. They might end up doing mini waterfall within their agile process. Many don’t even realize this. This results in poor quality and requires additional effort to “harden” the product. Potentially Shippable Product increment The acceptance criteria and DoD should be modified...
Post a Comment
Read more

Embracing Frequent Delivery: The Key to Success with modern product development

  One of my favorite story is about how Google Chrome surpassed Microsoft's Internet Explorer by leveraging its rapid release strategy. Without fail I repeat this in almost every training I give to my team.  In today's rapidly evolving digital landscape, the traditional approach of infrequent software releases is being replaced by a more agile and dynamic methodology: frequent delivery. Embracing frequent delivery not only enhances user experience but also enables organizations to stay ahead of the competition.  User Experience Frequent delivery empowers organizations to continuously improve their software products based on user feedback and evolving market demands. By rapidly addressing bugs, implementing enhancements, and introducing new features, organizations can provide an exceptional user experience. Many years ago, Internet Explorer(IE) was the most popular browser. There were many other small browsers but none had the reach of IE. Then google entered the market tr...
Post a Comment
Read more