Menu
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>
402
SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column.
XML data can be queried using an SQL fullselect or with the SQL/XML query functions of XMLQUERY and XMLTABLE. The XMLEXISTS predicate can also be used in SQL queries on XML data.
The xml data type is a built-in data type in SQL Server and is somewhat similar to other built-in types such as int and varchar. As with other built-in types, you can use the xml data type as a column type when you create a table as a variable type, a parameter type, a function-return type, or in CAST and CONVERT.
You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
