locked
Join on XML field in sql RRS feed

  • Question

  • User-1313211662 posted

    Hi,

    I have table

    Table 1

    col1       col2

    1           abc

    2          xyz

    ---------------------------------------------

    other table is Table 2 with xml column

    col1     col2

    a        <root><row value="abc"><row value="xyz"></root>

    b        -

    -----------------------------------------------------

    I have to join Table1 and table 2 on the basis of col2

    how can i achive it

    Monday, October 10, 2016 5:05 AM

Answers

  • User-595703101 posted

    Hello Sangad,

    You can parse XML data in SQL table table2 as follows

    select
    	table2.col1,
    	table2.col2,
    	xmlData.value('@value','nvarchar(10)') as rowValue
    from table2
    cross apply col2.nodes('/root/row') as T2Rows(xmlData)

    This will return you a data structure as follows with its data

    a	<root><row value="abc" /><row value="xyz" /></root>	abc
    a	<root><row value="abc" /><row value="xyz" /></root>	xyz

    Now you can join Table1 with above data from Table2 as follows

    select 
    *
    from table1 
    inner join (
    select
    	table2.col1,
    	table2.col2,
    	xmlData.value('@value','nvarchar(10)') as rowValue
    from table2
    cross apply col2.nodes('/root/row') as T2Rows(xmlData)
    ) t
    on t.rowValue = table1.col2

    I hope it helps,

    Please check SQL XML query tutorial on SQL Server for more samples

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 10, 2016 10:40 AM

All replies

  • User-595703101 posted

    Hello Sangad,

    You can parse XML data in SQL table table2 as follows

    select
    	table2.col1,
    	table2.col2,
    	xmlData.value('@value','nvarchar(10)') as rowValue
    from table2
    cross apply col2.nodes('/root/row') as T2Rows(xmlData)

    This will return you a data structure as follows with its data

    a	<root><row value="abc" /><row value="xyz" /></root>	abc
    a	<root><row value="abc" /><row value="xyz" /></root>	xyz

    Now you can join Table1 with above data from Table2 as follows

    select 
    *
    from table1 
    inner join (
    select
    	table2.col1,
    	table2.col2,
    	xmlData.value('@value','nvarchar(10)') as rowValue
    from table2
    cross apply col2.nodes('/root/row') as T2Rows(xmlData)
    ) t
    on t.rowValue = table1.col2

    I hope it helps,

    Please check SQL XML query tutorial on SQL Server for more samples

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 10, 2016 10:40 AM
  • User-1313211662 posted

    Thanks Eralper,

    I already apply the solution that you mentioned.

    But the issue with it is we need to split row first and then can join with other table,it will take lot time.

    Because my tables contains data in milion for that i used xml logic to merge some field in xml.

    Is any other solution to join before split rows

    Monday, October 10, 2016 10:46 AM
  • User-595703101 posted

    Why don't you create index on XML column?

    I'll check if it will help your query execute faster.

    If there is PK field with clustered index on it, you can create an XML index as follows

    CREATE PRIMARY XML INDEX IXML_Table2_Col2
        ON dbo.Table2 (Col2)  

    Please check Create XML Index syntax at BOL

    Otherwise the only solution will be using regular expressions or some other string functions for fetching the value attribute

    Monday, October 10, 2016 12:53 PM