none
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)')

    Monday, February 27, 2006 11:49 PM

Answers

  • 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.

    Tuesday, February 28, 2006 1:15 AM

All replies

  • 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.

    Tuesday, February 28, 2006 1:15 AM
  • 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?

    Tuesday, February 28, 2006 8:52 PM
  • Your query is correct. There is a small typo.

    You are missing

    @   before   sourceColumn="ProductID"

    Wednesday, March 01, 2006 9:43 PM
  •  

    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

    Thursday, March 02, 2006 9:30 PM
  • 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'.

     

    Monday, November 03, 2008 5:19 PM
  • 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')

     

    Wednesday, November 05, 2008 5:38 AM
  • 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.

     

    Wednesday, November 05, 2008 1:06 PM