question

Sorry, I did not realize that the request was to enable the dynamic querying of the tables' XML column, which was needed because different tables may have differently structured XML contents. I also needed to strike out the blob below about the WHILE loop because the answer which was using it (for whatever reason) has disappeared. The dynamic solution is under the original scripts. Please do not use any WHILE loops for simple data selections, they never make any sense at all. The only reason to even have the WHILE loop in T-SQL is for the scenarios when the data from the table or from the join of multiple tables needs to be fed into, say, stored procedure, which in turns needs to be executed for each row. This scenario is very rare, and even then it is possible to avoid the dreaded WHILE loop in most cases. When dealing with data in SQL Server databases, just change the way of thinking from "what can I do to a row" to that can I do to a column". This will help avoiding the so-called RBAR processing (row-by-agonizing-row), which never makes a lick of sense. For the sample data in question, the simple select statements below will do the trick: -- data from TableA select ID, item.value('ClientID[1]', 'varchar(50)') ClientID, item.value('Title[1]', 'varchar(50)') Title from TableA a cross apply TestXML.nodes('root') r(item); -- data from TableB select ID, item.value('ClientID[1]', 'varchar(50)') ClientID, item.value('Value[1]', 'varchar(50)') Value from TableB a cross apply TestXML.nodes('root') r(item) Based on the sample data in question, the selects return the following results: -- TableA data ID ClientID Title ------ ---------- -------- 1 AAAA XXXX 2 BBBB YYYYY -- TableB data ID ClientID Value ------ ---------- -------- 1 AAAA XXXX 2 BBBB YYYYY Because different tables have differently structured XML, the script above will not work well if the node names are different in different tables. In this case, it would be necessary to examine the XML structure and generate the column list dynamically. There is an excellent post by [Jacob Sebastian][1] titled [XQuery Lab 58 - SELECT * FROM XML][2]. It includes the definition of the inline table valued function named **XMLTable**. Once the function is created in the database, the dynamic querying of the data becomes a trivial exercise. Here is a complete solution which uses the function: -- XML structure from the TestXml column of the actual table, such as TableA declare @x xml = (select top 1 TestXml from TableA); -- the correctly formatted column list from the XMl structure declare @columnList nvarchar(max); -- dynamic script to execute declare @sql nvarchar(max); select @columnList = ( select char(10) + ', item.value(''' + XPath + ''', ''varchar(50)'') ' + NodeName from dbo.XMLTable(@x) where ParentName is not null for xml path('') ), @sql = ' select ID' + @columnList + ' from TableA a cross apply TestXML.nodes(''/'') r(item) '; exec (@sql); The script above can be easily modified to take the actual table name as a parameter and select the data from it expanding TestXML column as columns. Just to see how the function works, execute this: select a.*, x.NodeName, x.Value from dbo.TableA a cross apply dbo.XMLTable(TestXml) x where x.NodeName <> 'root'; The select above still needs to be pivoted with the dynamic pivot so the dynamic SQL solution I include is not any heavier than the possible solution with the dynamic pivot. Hope this helps, Oleg [1]: http://beyondrelational.com/members/jacobbr/default.aspx [2]: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx