October 30, 2008 at 3:49 pm
Hii
Thanks in advance
I have a product table and Product Group table...
I need to make a join between the Product table and and Xml data type fields of Product Group Table...
I am new to Sql.....so please help me if u hav any idea?
October 31, 2008 at 7:16 am
Wow, not a fun or real performant thing to do. First you need to return the data from the XML column of the product group table as a table then join on it. Something like:
Select
column list
From
products P Join
(
Select
X.column.value('[element or attribute name]', '[data type]') as xml_column
From
product_groups PG Cross Apply
xml_column.nodes('[xpath]') as X(column)
) PG On
P.key_field = PG.xml_column
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 10:37 am
Thanks for ur response...
but when i tried that code...i am getting the following error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'list'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'column'.
October 31, 2008 at 10:44 am
Where I put "column list" you need to supply the list of columns you want to return. That was just a place holder since you did not post any schema information.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 10:52 am
Sorry...
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'.
October 31, 2008 at 11:10 am
It is hard to provide you with the code you need since you haven't provided us with anything to really help you. Read the article below, it will so you how to help us help you.
Since you have an XML column, be sure to also provide the details of the XML document.
October 31, 2008 at 11:10 am
Instead of X(column) do X(ProductId) or something else. And the [XPATH] needs to be replaced with the XPath expression that will navigate to the product id.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 11:22 am
How should i get the Xpath.?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply