This will give me a single line with a list of MyText attributes,
recursively, under the node.

select
FileTreePrivatexml.query('data(//Node[@SomeAttribute="MyKey"]//@MyText)')
from tuser where UserID=1

So the output will be a list of MyText values separated with a space, so it
might look like:
abc cccddf fdsfsd sdfsdf adsada asdas

I need to get these into a select statement, in a list somehow, so I can put
into a temp table, or a Table variable, so I can then compare to a live
relational table.

I have found the code below which brilliantly gives an example of how to
list out attributes, but I cannot convert it to properly work on my item
above. Can somebody help?

If you run the code you will see a nice table of attributes.

DECLARE @RuleXML XML
SET @RuleXML =
'<rule id="100100001">
<conditions>
<filter type="and">
<condition module="person" attribute="age" operator="gt">
<value>35</value>
</condition>
<condition module="transportation" attribute="automobile"
operator="neq">
<value>truck</value>
</condition>
<condition module="family" attribute="spouse" operator="eq">
<value>wife</value>
<value>child</value>
</condition>
<condition module="job" attribute="description" operator="eq">
<value>receptionist</value>
<value>sales</value>
</condition>
</filter>
</conditions>
</rule>'

SELECT
Attribute = N.c.value('(../@attribute)', 'nvarchar(4000)')
,Value = N.c.value('(.)', 'nvarchar(4000)')
,Operator = N.c.value('(../@operator)', 'nvarchar(4000)')
FROM @RuleXML.nodes('/rule/conditions/filter/condition/value') AS N(c)
WHERE N.c.value('(.)', 'nvarchar(4000)') != ''


This gives the output:
age 35 gt
automobile truck neq
spouse wife eq
spouse child eq
description receptionist eq
description sales eq

Derek Hart wrote:
Rougly like this, check the documentation of the 'nodes' method in the
SQL server documentation:

SELECT
T.mt.value('.', 'nvarchar(100)') AS MyText
FROM tuser
CROSS APPLY
FileTreePrivatexml.nodes('//Node[@SomeAttribute="MyKey"]//@MyText') as T(mt)
WHERE UserID=1

--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

Worked great Martin... question on this... my goal was to get a count,
joining the list of attributes to a table...

Is this an efficient process? Basically it is exactly what you sent me with
an IN clause. My xml field, FileTreePrivateXML is a typed field with a
schema attached.

The other question is that I cannot figure out why the sql management studio
pane underlines part of this, such as FileTreePrivatexml.nodes, with the
error "Invalid Object Name 'FileTreePrivatexml.nodes'" - And the part
T.mt.value is underlined with the error 'Cannot find either column T or the
user-defined or aggregate 'T.mt.value', or the name is ambiguous.' Any idea
on that. Do I need to preface something with dbo. ?

Select COUNT(*) As RCount From tFile Where UserID = 1 And MyText IN
(SELECT T.mt.value('.', 'varchar(50)') AS MyText FROM tuser
CROSS APPLY
FileTreePrivatexml.nodes('//Node[@SomeAttribute="MyKey"]//@MyText') as
T(mt)
WHERE UserID=1)


"Martin Honnen" <mahotrash (AT) yahoo (DOT) de> wrote

Derek Hart wrote:
I don't know, I am afraid I don't have enough experience with SQL server
and writing efficient or optimized queries.

Do you get an error when trying to execute the query? Or is that simply
SQL management studio complaining?

Does the part in parentheses (i.e.
SELECT T.mt.value('.', 'varchar(50)') AS MyText FROM tuser
CROSS APPLY
FileTreePrivatexml.nodes('//Node[@SomeAttribute="MyKey"]//@MyText') as T(mt)
WHERE UserID=1
) work when you execute/try it alone?
I am currently not sure why it would cause problems when put in an IN
clause.

--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

Query works fine. Just the red underlines, sql management is complaining.

"Martin Honnen" <mahotrash (AT) yahoo (DOT) de> wrote

Derek Hart wrote:
I don't know why that happens, maybe the SQL management studio parser is
not up to date for that CROSS APPLY/nodes stuff.

--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/