Doing a join between XML data typed field and a relational table in Yukon (SQL 2005)

Hello,
I have a Yukon XML question and would be very happy if someone can
assist:
Let's assume I have 2 tables:
One will be product table Regular relational table with Product ID
as a primary key and some descriptive fields.
Second is an Order table it has Order ID as a PK and Order details
which is a field of XML data type and holds all lines in the order
including quantity, product id, prices, etc.
I want to make a join that will present the orders with all product
details for each order line.
Do you know how can I do a Join between XML field and relational
table?
Thanks,
Nitsan
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
[snip]
> Do you know how can I do a Join between XML field and relational
> table?
You should be able to do this with the new nesting of FOR XML statements in
Yukon. See:
http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
Bryant
|||Thanks for your answer,
I looked into it and it looks like it was doing select on relational tables
and just created XML as an answer.
I am looking for the opposite - Query in XML content join relational table
to get data (even not necessarily in XML format).
Thanks,
Nitsan
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> [snip]
> You should be able to do this with the new nesting of FOR XML statements
in
> Yukon. See:
> http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
> --
> Bryant
>
|||Even outside of the capabilties of FOR XML, you can do what you want.
You can use a value from the XML data type using the method xmltype.value()
in a SQL predicate. Or you can use the value of a sql:variable or sql:column
to do the join inside an XQuery.
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:ccuhjj$kr2$1@.news01.intel.com...
> Thanks for your answer,
> I looked into it and it looks like it was doing select on relational
tables
> and just created XML as an answer.
> I am looking for the opposite - Query in XML content join relational table
> to get data (even not necessarily in XML format).
> Thanks,
> Nitsan
> "Bryant Likes" <bryant@.suespammers.org> wrote in message
> news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> in
>
|||Nitsan,
in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
Preview you can use nodes() method on XML type to get a row set out of an
XML instance and then do the join in T-SQL space:
create table products(ProdID int primary key, ProdDesc nvarchar(max))
go
create table orders(OrderID int primary key, OrdDetails xml)
go
insert products values(1, 'Prod1')
insert products values(2, 'Prod2')
go
insert orders values(1,
'<prod id="1">
<quantity>2</quantity>
<price>12345.67</price>
</prod>
<prod id="2">
<quantity>3</quantity>
<price>9876.54</price>
</prod>')
go
select OrderID, quantity, price, ProdDesc
from
(
select
OrderID,
prod.value('./@.id', 'int') as id,
prod.value('(./quantity)[1]', 'int') as quantity,
prod.value('(./price)[1]', 'money') as price
from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
) as OrdLine
join products on OrdLine.id=products.ProdID
go
You can then apply FOR XML to results if you need results as XML.
Regards,
Eugene Kogan,
Technical Lead,
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> Hello,
> I have a Yukon XML question and would be very happy if someone can
> assist:
> Let's assume I have 2 tables:
> One will be product table - Regular relational table with Product ID
> as a primary key and some descriptive fields.
> Second is an Order table - it has Order ID as a PK and Order details
> which is a field of XML data type and holds all lines in the order
> including quantity, product id, prices, etc.
> I want to make a join that will present the orders with all product
> details for each order line.
> Do you know how can I do a Join between XML field and relational
> table?
> Thanks,
> Nitsan
|||Thanks,
Do you have a short example if usin git for this purpose?
"Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Even outside of the capabilties of FOR XML, you can do what you want.
> You can use a value from the XML data type using the method
xmltype.value()
> in a SQL predicate. Or you can use the value of a sql:variable or
sql:column[vbcol=seagreen]
> to do the join inside an XQuery.
> Bob Beauchemin
> http://staff.develop.com/bobb
>
> "Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
> news:ccuhjj$kr2$1@.news01.intel.com...
> tables
table[vbcol=seagreen]
statements
>
|||Thank you very much !!!
"Eugene Kogan" <eugene_kogan@.hotmail.com> wrote in message
news:uUd0kAJaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Nitsan,
> in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
> Preview you can use nodes() method on XML type to get a row set out of an
> XML instance and then do the join in T-SQL space:
> create table products(ProdID int primary key, ProdDesc nvarchar(max))
> go
> create table orders(OrderID int primary key, OrdDetails xml)
> go
> insert products values(1, 'Prod1')
> insert products values(2, 'Prod2')
> go
> insert orders values(1,
> '<prod id="1">
> <quantity>2</quantity>
> <price>12345.67</price>
> </prod>
> <prod id="2">
> <quantity>3</quantity>
> <price>9876.54</price>
> </prod>')
> go
> select OrderID, quantity, price, ProdDesc
> from
> (
> select
> OrderID,
> prod.value('./@.id', 'int') as id,
> prod.value('(./quantity)[1]', 'int') as quantity,
> prod.value('(./price)[1]', 'money') as price
> from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
> ) as OrdLine
> join products on OrdLine.id=products.ProdID
> go
> You can then apply FOR XML to results if you need results as XML.
>
> Regards,
> Eugene Kogan,
> Technical Lead,
> SQL Server Engine
> Microsoft Corp
> [This posting is provided "AS IS" with no warranties, and confers no
> rights.]
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
>
|||Hi Nitsan,
Here's a really short example, starting with the authors table in the pubs
database:
create table xmlauth (
authbio xml)
go
insert xmlauth values('<author id="172-32-1176"><hobbies><golf score="78"
/><tennis/></hobbies></author>')
insert xmlauth values('<author id="213-46-8915"><hobbies><golf score="82"
/><bowling/></hobbies></author>')
If you know each document contains one author, you can use xml.value:
select authbio.query('/author/hobbies/*') as hobbies, au_lname
from authors, xmlauth
where authors.au_id =
xmlauth.authbio.value('author[1]/@.id', 'varchar(11)')
(if you have a schema collection which specifies a single author occurance,
you can leave out the subscript [1] in the value subquery)
If documents may contain more than one author, (e.g)
insert xmlauth values('
<authors>
<author id="274-80-9391"><hobbies><swimming
/><bowling/></hobbies></author>
<author id="274-80-9391"><hobbies><baseball
/><bowling/></hobbies></author>
</authors>')
you would need xml.nodes. Eugene's excellent example illustrates that.
You can even do this (caution: NOT recommended, cartesian product) using the
SQL value in the XQuery:
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where hobbies.exist('/*')=1
or
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where convert(nvarchar(100), hobbies) <> ''
Hope this helps,
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:cd034u$fo8$1@.news01.intel.com...
> Thanks,
> Do you have a short example if usin git for this purpose?
> "Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
> news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> xmltype.value()
> sql:column
> table
> statements
>