XML Data Type In SQL Server 2008 Query

Question

I have a table in SQL Server in which one of the columns is an XML data type. There are other columns in the table that are not XML. The following is an example XML that is stored in a column:

<AdultAsthma> <Group> <Question text="Act Score:" ForeColor="Green" /> <Controls> <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black" /> <Control type="TextBox" id="txtActScore" Answer="" /> </Controls> </Group> </AdultAsthma> 

What I want is a query that matches some values ​​in other columns in the table and for the corresponding columns, I want to get the text attribute from the Node Question and the response attribute from the node control. Can someone help me with this?

EDIT

What needs to be changed if I have several node groups? In this scenerio, I would like the text of each question and the answer to match each question. See below:

 <AdultAsthma> <Group> <Question text="Act Score:" ForeColor="Green" /> <Controls> <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black" /> <Control type="TextBox" id="txtActScore" Answer="" /> </Controls> </Group> <Group> <Question text="Do You Have Asthma?:" ForeColor="Black" /> <Controls> <Control type="RadioButton" id="rbHaveAsthmaYes" text="Yes" GroupName="Diagnosed" ForeColor="Black" Answer="False" /> <Control type="RadioButton" id="rbHaveAsthmaNo" text="No" GroupName="Diagnosed" ForeColor="Black" Answer="False" /> </Controls> </Group> </AdultAsthma> 

Answer 1

 declare @T table ( XMLCol xml ) insert into @T values ('<AdultAsthma> <Group> <Question text="Act Score:" ForeColor="Green" /> <Controls> <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black"/> <Control type="TextBox" id="txtActScore" Answer="Answer" /> </Controls> </Group> </AdultAsthma> ') select XMLCol.value(N'(/AdultAsthma/Group/Question/@text)[1]', 'nvarchar(max)'), XMLCol.value(N'(/AdultAsthma/Group/Controls/Control/@Answer)[1]', 'nvarchar(max)') from @T 

Update:

When you need to trim your XML to multiple lines, you can use .nodes() in cross apply .

 declare @T table ( XMLCol xml ) insert into @T values ('<AdultAsthma> <Group> <Question text="Act Score:" ForeColor="Green" /> <Controls> <Control type="Label" id="txtScore" text="Enter ACT Score:" ForeColor="Black" /> <Control type="TextBox" id="txtActScore" Answer="" /> </Controls> </Group> <Group> <Question text="Do You Have Asthma?:" ForeColor="Black" /> <Controls> <Control type="RadioButton" id="rbHaveAsthmaYes" text="Yes" GroupName="Diagnosed" ForeColor="Black" Answer="False" /> <Control type="RadioButton" id="rbHaveAsthmaNo" text="No" GroupName="Diagnosed" ForeColor="Black" Answer="False" /> </Controls> </Group> </AdultAsthma> ') select XNvalue(N'(Question/@text)[1]', 'nvarchar(max)'), XNvalue(N'(Controls/Control/@Answer)[1]', 'nvarchar(max)') from @T as T cross apply T.XMLCol.nodes(N'/AdultAsthma/Group') as X(N)