SOLVED: XQuery - a new row for every element

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SOLVED: XQuery - a new row for every element

Author

Topic

ArnoSmulders
Starting Member

3 Posts

Posted - 2009-06-03 : 09:13:33

Hello,

I'm pretty new to XQuery, so hopefully my question can be answered easilly.

In my SQL Server 2005 database I've got a table with a XML column. For example:
Table: MyTable
Columns:
PK int
Data xml

An example of the data within the Data column could look like this:
Record 1
<data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<field id="Question1" type="Choice" isRelevant="true">1</field>
<field id="Question2" type="Choice" isRelevant="true">0</field>
</data>

Record 2
<data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<field id="Question1" type="Choice" isRelevant="true">1</field>
</data>

Now I want to run a query, so I can get a resultset of all field elements and each field element should be returned in a separate row.

So far I've got the following query:
select Data.query('/data/field') from MyTable

This query returns all field elements, but not in separate rows. Like this:
<field id="Question1" type="Choice" isRelevant="true">1</field><field id="Question2" type="Choice" isRelevant="true">0</field>
<field id="Question1" type="Choice" isRelevant="true">1</field>

Who can help me in getting this puzzle solved? Any clue will be greatly appreciated.

Arno

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-03 : 09:34:48

I think you want to use the nodes function, not the query for this. I'll see if I can find an example.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

ArnoSmulders
Starting Member

3 Posts

Posted - 2009-06-03 : 10:16:27

Well, even with only your hint of using Nodes() pushed me into the right direction of solving this problem.

The following statement was the solution:
select MT.tempColumn.query('.') as result
from MyTable
CROSS APPLY Data.nodes('/data/field') as MT(tempColumn)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources