Reputation: 2416
I have some xml stored in an varchar column and the id in the attributes of some of those nodes is a key in another table. How can I do a join on multiple attributes? I know the value method below only returns one id (and the first one at that).
create table #table1 (id1 int, col1 varchar(50));
create table #table2 (id2 varchar(1), col2 varchar(4));
insert into #table1 (id1, col1) values
('1', '<a><b id="1"></b><b id="2"></b></a>');
insert into #table2 (id2, col2) values
('1', 'val1'),
('2', 'val2');
select * from #table1 t1
left join #table2 t2 on t2.id2 = cast(t1.col1 as xml).value('(/a/b/@id)[1]', 'varchar(1)')
Returns
id1 col1 id2 col2
1 <a><b id="1"></b><b id="2"></b></a> 1 val1
How can I get another row returned for table2's ID 2?
1 <a><b id="1"></b><b id="2"></b></a> 2 val2
Upvotes: 1
Views: 596
Reputation: 12940
this would be much easier if your types were correct :)
CREATE TABLE #table1 ( id1 INT, col1 VARCHAR(50) );
CREATE TABLE #table2
(
id2 VARCHAR(1)
, col2 VARCHAR(4)
);
INSERT INTO #table1
( id1
, col1
)
VALUES ( '1'
, '<a><b id="1"></b><b id="2"></b></a>'
);
INSERT INTO #table2
( id2, col2 )
VALUES ( '1', 'val1' ),
( '2', 'val2' );
;
WITH c AS ( SELECT id1
, col1 = CONVERT(XML, col1)
FROM #table1 t
)
SELECT id1
, col1
, id2
, col2
FROM c
CROSS APPLY c.col1.nodes('(/a/b)') q ( x )
LEFT JOIN #table2 t2 ON t2.ID2 = x.value('./@id', 'varchar(1)')
DROP TABLE #table1, #table2
Upvotes: 1