SQL Server Dynamic XQuery

Question

I'm trying to pull data from XML (stored as NText) in a SQL Table.

Suppose we have two tables, each with XML:

|          TABLE 1          |         |     TABLE 2    |
|ID| NAME  |FIELD_DEFINITION|         |ID|DEF_ID|VALUES|
|1 |FIELD 1|     <XML1>     |         |1 |   1  |<XML2>|
|---------------------------|         |----------------|

And suppose that rows 1 and 2 of XML1 looks like so:

ROW 1
-----
<def>
    <prop name="Property 1" pdid="1"/>
    <prop name="Property 2" pdid="2"/>
</def>

ROW 2
-----
<def>
    <prop name="Property 1" pdid="3"/>
    <prop name="Property 2" pdid="4"/>
</def>

And XML2 looks like so:

ROW 1
-----
<ps>
    <p pdid="1" pvalue="Value 1"/>
    <p pdid="2" pvalue="Value 2"/>
</ps>

ROW 2
-----
<ps>
    <p pdid="3" pvalue="Value 3"/>
    <p pdid="4" pvalue="Value 4"/>
</ps>

I'm trying to get all values for any properties named "Property 1" however the definition of the XML that denotes where the value is stored in Table 1 and the values are stored in Table 2.

I'm getting the pdid of the Property 1 field for each entry in Table 1 like so:

SELECT
    t1.ID, t1.NAME,
    CAST(t1.FIELD_DEFINITION AS XML).value('(/def/prop[@name = "Property 1"]/@pdid)[1]','varchar(10)') as FIELD_ID
FROM
    [Table 1] t1

But how do I now pass that pdid value into an XQuery to pull the pvalue from Table 2? I was hoping I could do the above, and join Table 2 to Table 1 on t1.ID = t2.DEF_ID and then pass cp.FIELD_ID into the XQuery on t2.VALUES.value().

Is this possible? Or am I taking the wrong approach here?

score 0 · Accepted Answer · answered Sep 16 '14 at 14:08

After much head-banging (and "re-allocating" my time away from other work), I've managed to do it, I'll post here for anybody interested.

SELECT
    t1.ID AS T1_ID, t1.NAME, t2.ID AS T2_ID,
    CAST(CAST(t1.FIELD_DEFINITION AS NVARCHAR(MAX)) + CAST(t2.VALUES AS NVARCHAR(MAX)) AS XML).value
    ('for $d in (/def/prop[@name = "Property 1"]/@pdid)[1] return (/ps/p[@pdid = $d]/@pvalue)[1]','varchar(50)') as VAL
FROM
    [Table 1] t1 INNER JOIN
    [Table 2] t2 ON t1.ID = t2.DEF_ID

Good old FLWOR came to the rescue. Playing with XML in SQL will never be quick (~6s to return 1000 rows in my case) but it gets the job done!

SQL Server Dynamic XQuery

1 Answers1