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 Datatype. There are other columns in the table that are not XML. Here is an example of the XML that is stored in the 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 on the other columns in the table and for those columns that match, I want to get the text attribute from the Question Node and the Answer attribute from the Control node. Can someone help me with this?

EDIT

What needs to be changed if I have more than one Group node? In this scenerio, I would want the text of each question and the answer to go along with 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>

Give this a good look over: msdn.microsoft.com/en-us/library/ms178030.aspx — RThomas
– RThomas, Commented Mar 14, 2012 at 19:16

Mikael Eriksson · Accepted Answer · 2012-03-14 19:58:05Z

6

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 shred your XML to multiple rows you can use .nodes() in a 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 X.N.value(N'(Question/@text)[1]', 'nvarchar(max)'),
       X.N.value(N'(Controls/Control/@Answer)[1]', 'nvarchar(max)')
from @T as T
  cross apply T.XMLCol.nodes(N'/AdultAsthma/Group') as X(N)

edited Mar 14, 2012 at 19:58

answered Mar 14, 2012 at 19:13

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric R. Over a year ago

this works great! What should I change if I have multiple groups nodes under the AdultAsthma node? See my edit.

Collectives™ on Stack Overflow

XML Data Type In SQL Server 2008 Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related