Using data from XML column as basis for LEFT JOIN...

Question

I have a primary key in table1 in a single column of type int.  I have another table "table2" that has an XML column.  This XML column has values from the primary key in table1.  I would like to return rows from table1 where the primary key in table1 matches data inside the XML column in table2 (the primary key).  I have worked with CROSS APPLY, but cannot seem to use it to replace the LEFT OUTER JOIN syntax.  I know the following syntax is wrong, but I posted it so you can get a feel for what I'm trying to do.

SELECT table1.ProductID

FROM table1

LEFT OUTER JOIN (CROSS APPLY table2.ScdHash.nodes('//type0') AS NewTable(MyTable))

ON table1.ProductID = MyTable.value('(//value)[1]', 'nvarchar(30)')

Answer 1

One simple way to do this is to use sql:column like below:

SELECT t1.* FROM t1 JOIN t2 on t2.x.exist ('/foo/bar[.=sql:column("t1.i")]') = 1

This will make use of the XML indexes if they are available. If you know that the property you are looking for inside the XML document is a singleton (meaning there is only path to /foo/bar), then you can easily promote the property to a computed column and use that column in your query.

Eg:
CREATE FUNCTION temp_sp (@xData xml)
RETURNS numeric (10,2)
WITH schemabinding
BEGIN
          DECLARE @curretnValue numeric (10,2)
          SELECT @curretnValue = @xData.value('(/foo/bar)[1]', 'numeric(10,2)')
          RETURN @curretnValue
END

ALTER TABLE t2 ADD CurrentValue AS dbo.temp_sp (x) PERSISTED
create index cv_idx on t2(CurrentValue)

select t1.* from t1 JOIN t2 on t2.currentValue = t1.i -- Note that I am using the computed column here.

The article http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql25xmlbp.asp has more information about the computed column.

Answer 2

Thanks for the prompt response.  It gave me a lot to work with, but now I have another question.  The following XML is in the XML column in table2 (d_Product).

<type0 sourceColumn="ProductID">

<sourceData>

<table name="r_ProductNew">

<column name="ProductID">

<value>21000005</value>

</column>

</table>

</sourceData>

</type0>

Here is the statement I built after reading your post.

SELECT r_ProductNew.ProductID, d_Product.ScdHash

FROM r_ProductNew

JOIN d_Product

ON ScdHash.exist('/type0[sourceColumn="ProductID"]/sourceData/table[@name="r_ProductNew"]/column[@name="ProductID"]/value[.=21000005]') = 1

From the query, you can see that I only want to return rows from d_ProductNew where the corresponding value in d_Product xml column value is 21000005.  However, after executing the query, I get no results back. 

Any ideas?

Answer 3

Your query is correct. There is a small typo.

You are missing

@   before   sourceColumn="ProductID"

Answer 4

 

Also if you want to use the value from a column inside your xpath, you need to use sql:column. You current query below will result in a cross product of all the rows from r_ProductNew and the matching rows (rows that match the exist condition) from d_product.

SELECT r_ProductNew.ProductID, d_Product.ScdHash

FROM r_ProductNew

JOIN d_Product

ON ScdHash.exist('/type0[@sourceColumn="ProductID"]/sourceData/table[@name="r_ProductNew"]/column[@name="ProductID"]/value[.=21000005]') = 1

To get the effect you want you may rewrite the query as follows:

SELECT r_ProductNew.ProductID, d_Product.ScdHash

FROM r_ProductNew

JOIN d_Product

ON ScdHash.exist('/type0[@sourceColumn="ProductID"]/sourceData/table[@name="r_ProductNew"]/column[@name="ProductID"]/value[.=sql:column("r_ProductNew.ProductID"]') = 1 and r_ProductNew.ProductID = 21000005

Thanks

Babu

Answer 5

have two tables..one is ProductBase and other one is ProductGroup...

I need to make a join between ProductId of Producttable(Primary key) and ProductID which is xml datatype

Please help me
I used the code like this...
Select
        dbo.ProductBase.ProductId
From
        productBase P Join
        (
        Select
                X.column.value('[ProductID]', '[uniqueidentifier]') as xml_column
        From
                product_group PG Cross Apply
                xml_column.nodes('[xpath]') as X(column)
         PG On
                P.key_field = PG.xml_column
then i am getting the following error
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'column'.

 

Answer 6

Another way of doing it

 

Declare @tblProduct table(ProdID int,ProdName varchar(100))

Insert into @tblProduct values (1,'Prod1')

Insert into @tblProduct values (2,'Prod2')

Insert into @tblProduct values (3,'Prod3')

Insert into @tblProduct values (4,'Prod4')

Declare @tblInfo table(InfoID int,col2 varchar(100),InfoXML xml)

Insert into @tblInfo values (1,'ProdInfo1',

'<type0 sourceColumn="ProductID">

<sourceData>

<table name="r_ProductNew">

<column name="ProductID">

<value>1</value>

</column>

</table>

</sourceData>

</type0>

')

Insert into @tblInfo values (2,'ProdInfo2',

'<type0 sourceColumn="ProductID">

<sourceData>

<table name="r_ProductNew">

<column name="ProductID">

<value>2</value>

</column>

</table>

</sourceData>

</type0>

')

 

Insert into @tblInfo values (3,'ProdInfo3',

'<type0 sourceColumn="ProductID">

<sourceData>

<table name="r_ProductNew">

<column name="ProductID">

<value>3</value>

</column>

</table>

</sourceData>

</type0>

')

 

 

--Left join implementation

SELECT a.ProdID,a.ProdName,b.col2,b.InfoXML.value('(type0/sourceData/table/column/value)[1]','int')

FROM @tblProduct a

LEFT OUTER JOIN @tblInfo b

ON a.ProdID = b.InfoXML.value('(type0/sourceData/table/column/value)[1]','int')

--Inner join implementation

SELECT a.ProdID,a.ProdName,b.col2,b.InfoXML.value('(type0/sourceData/table/column/value)[1]','int')

FROM @tblProduct a

Inner JOIN @tblInfo b

ON a.ProdID = b.InfoXML.value('(type0/sourceData/table/column/value)[1]','int')

 

Answer 7

That is because 'column' is a reserved word in SQL.  Give it a different name.  Do not use reserved words for object or column names.