No responses - no doubt becuase this was more a SQL than Umbraco question. Though fyi and for my own reference here is the 'quickie' solution I came up with for working with the xml defined properties in relational / normalised form.

Create a view 'vwMemberResponses'  that had the xml stored as XML datatype a opposed to ntext datatype of CMSContentXml table.

SELECT     CCX.nodeId, CONVERT(XML, CONVERT(NVARCHAR(MAX), CCX.xml)) AS XML
FROM         dbo.cmsContent AS CC INNER JOIN
                      dbo.cmsContentXml AS CCX ON CC.nodeId = CCX.nodeId
WHERE     (CC.contentType = 1065) -- 1065 is one of a few Doc Types I will be normalising.

Creating a view upon the above view which resulted in each property getting its own column:

SELECT     

r.value('(/Question/@id)[1]', 'varchar(50)') AS Id,
r.value('(/Question/@parentID)[1]', 'varchar(50)')  AS ParentId,
-- USING APLPHABET FOLDER, SO GRAB "REAL" VALUE TO KEY ON.
umbraco45.dbo.GetPathPortion(     r.value('(/Question/@path)[1]', 'varchar(50)')  ,   3  ) AS ParentParentId,
r.value('(/Question/@createDate)[1]', 'varchar(50)') AS CreateDate,                       
r.value('(/Question/questionText/text())[1]', 'varchar(max)') AS QuestionText,
r.value('(/Question/@path)[1]', 'varchar(50)') AS Path,                    
r.value('(/Question/responseSet/text())[1]', 'varchar(max)') AS ResponseSet

FROM umbraco45.dbo.vwQuestions CROSS APPLY Xml.nodes('.') AS x(r)

To note that the first view and the second could actually be condensed to a single view.

Joining related tables

(As in this case I had the concept of questions with responses (1 to many) I found I could string search against the path attribute that appears on Umbraco nodes/tables (and now as its own column in the view). This gave me the neccasarry node id's to key on once I had done the above steps to create 2 views (one for the responses, and one for the questions).

select  dbo.GetPathPortion( Path , 2 ) as ParentQuestion

(In my case, as I am using Alphabet folders, the actual key is 2 nodes up instead of 1)

First draft of a dbo.GetPathPortion SQL function:

CREATE FUNCTION [dbo].[GetPathPortion]
(
    -- Add the parameters for the function here
    @string varchar(500),
    @index int 
)
RETURNS varchar(100)
AS
BEGIN
        declare @pos int
        declare @piece varchar(500)
        declare @count int
        set @count = 0

        -- Need to tack a delimiter onto the end of
        -- the input string if one doesn't exist
        if right(rtrim(@string),1) <> ','
        set @string = @string  + ','
       
        set @pos = patindex('%,%' , @string)
       
        while @pos <> 0
        BEGIN
            set @count = @count + 1
            set @piece = left(@string, @pos - 1)
            if(@index = @count) return @piece
            set @string = stuff(@string, 1, @pos, '')
            set @pos =  patindex('%,%' , @string)
       
        END
        -- Note: if @count is less than @index, the last @peice is returned.
        return @piece

END

Summary

I may well have done more work than was neccasarry (keen to know how this SHOULD have been done in Umbraco land) as I'm not that used to Umbraco API's and data model. But this has let me work with the Umraco document types and still work against a non xml and normalised data structure (which I really needed).

is working on a reply...