Querying from a XML Column across multiple rows

Question

In SQL Server I am trying to query against a table which has an XML column, inside that column is XML similar to below

DECLARE @XML AS XML

SET @XML =
'<Root>
    <Stuff>
        <InsideStuff>1</InsideStuff>
        <InsideStuff>2</InsideStuff>
        <StuffIDontWant>9</StuffIDontWant>
    </Stuff>
    <Stuff>
        <InsideStuff>3</InsideStuff>
        <InsideStuff>4</InsideStuff>
        <StuffIDontWant>6</StuffIDontWant>
        <StuffIDontWant>7</StuffIDontWant>
    </Stuff>
</Root>'

SELECT TableAlias.ColumnAlias.value('(.)','nvarchar(max)') 
FROM @XML.nodes('/Root/Stuff/InsideStuff') AS TableAlias(ColumnAlias)

The above code will give me 1,2,3,4 which is what I want, however I can't quite seem to figure out how to work this into a join format where I want to do something like

SELECT T1.Name 
FROM TableOne T1
INNER JOIN TableWithXml.ColumnWithXml.nodes('/Root/Stuff/InsideStuff') AS TableAlias(ColumnAlias) ON T1.ID = TableAlias(ColumnAlias)

How would I format my T-SQL in this scenario?

@philip-loyer So to understand the question, you want to join a table with an 'InsideStuffId' to entries in another table with an XML column, based on whether that column's value contains an 'InsideStuff' entry with the value given? Do the XML values to be joined on need to have multiple elements with the same name (ie. 'InsideStuff'), or should they only have one? It would make the XQuery easier I believe. — Fiddles
– Fiddles, Commented Jun 20, 2014 at 0:13
In my scenario one table has columns with say a integer value i want to join that on a xml column in a another table which has say 4 matching integer in the xml. I want to be able to know what integers are in the xml and in the other table — Philip Loyer
– Philip Loyer, Commented Jun 20, 2014 at 3:12

Fiddles · Accepted Answer · 2014-06-20 03:19:45Z

The main goal is to flatten the XML out to the required level, into a table you can use in your queries.

Assumed table structures:

DECLARE @TableWithXml TABLE (
    ID int IDENTITY(1,1),
    XmlCol XML
);

DECLARE @TableOne TABLE (
    ID int IDENTITY(1,1),
    StuffId int
);

Solution 1: If you don't care about any of the other data except whether the xml contains the given value, just flatten it one level and check the values: (sqlfiddle):

SELECT t1.ID as T1ID, t2.ID as TXID, t2.XmlCol
FROM @TableOne t1
CROSS JOIN @TableWithXml t2
WHERE t1.StuffId IN (
    SELECT      XmlData.InsideStuff.value('(.)','int') as InsideStuff
    FROM        @TableWithXml tX
    CROSS APPLY tX.XmlCol.nodes('/Root/Stuff/InsideStuff') as XmlData(InsideStuff)
    WHERE       tX.ID = t2.ID
);

-- // Or alternatively...

SELECT      t1.ID as T1ID, FlattenedTable.TXID, FlattenedTable.InsideStuff, FlattenedTable.XmlCol
FROM        @TableOne t1
INNER JOIN  (
    SELECT      tx.ID as TXID, tx.XmlCol, XmlData.InsideStuff.value('(.)','int') as InsideStuff
    FROM        @TableWithXml tX
    CROSS APPLY tX.XmlCol.nodes('/Root/Stuff/InsideStuff') as XmlData(InsideStuff)
) as FlattenedTable ON t1.StuffId = FlattenedTable.InsideStuff

Solution 2: If you want to work with the XML data, flatten it the whole way (sqlfiddle):

SELECT      t1.ID as T1ID,
            tx.ID as TXID, 
            Stuffs_InsideStuff.val.value('(.)', 'int') as InsideStuffId, 
            Stuffs_StuffIDontWant.val.value('(.)', 'nvarchar(max)') as StuffIDontWant
FROM        @TableOne t1
INNER JOIN  (
    @TableWithXml tX
    CROSS APPLY tX.xmlCol.nodes('/Root/Stuff') as XmlData(Stuffs)
    CROSS APPLY XmlData.Stuffs.nodes('InsideStuff') as Stuffs_InsideStuff(val)
    CROSS APPLY XmlData.Stuffs.nodes('StuffIDontWant') as Stuffs_StuffIDontWant(val)
) on t1.stuffId = Stuffs_InsideStuff.val.value('(.)', 'int');

Thank you! You have helped me learn more about XML in SQL than i ever really wanted to know :)

Collectives™ on Stack Overflow

Querying from a XML Column across multiple rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related