Query from 2 XML data sources

Hi,
Suppose I have to XML documents. The first one contains a list of
authors and the second one contains a list of books. For example,
author.xml
<authors>
<author @.id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author @.id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author @.id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>
book.xml
<catalog>
<book>
<title>SQL Server for Dummies</title>
<author @.id="1">Tom</author>
</book>
<book>
<title>XQuery for Dummies</title>
<author @.id="2">Chris</author>
</book>
<book>
<title>It's All About XML</title>
<author @.id="3">Jack</author>
</book>
</catalog>
Suppose I store "author.xml" as xml data type in the AUTHOR table and
"book.xml" as xml data type in the BOOK table. I would like to get the
information of authors who have written a book with title containing
"XQuery".
How can I write XQuery in SQL Server 2005 ?
Thanks
First - see also the item (in this forum) titled "Cross Joining two
XML columns, or two (or more) XML variables" (it also got renamed a
few times in the duration).
Well, firstly I wouldn't store them as entire documents. This data is
essentially in tabular form, so I would have an AUTHORS table (id,
name, tel) etc.
If I had to use xml (to store semi-structured data), then I would
probably start with AUTHORS (xml) (one row per author - which you can
do by shredding the original xml quite easily [shown]) -then add some
computed, stored, indexed columns. This means that in the database you
have access to a readonly column that is the "@.id" - this allows you
to join very efficiently between the tables, by using the pre-computed
column on the destination, not an xml query - i.e.
DROP TABLE AUTHORS
DROP FUNCTION GetAuthorId
GO
CREATE TABLE AUTHORS ([Content] xml NOT NULL)
GO
CREATE FUNCTION [dbo].GetAuthorId(@.Content xml) RETURNS int
WITH SCHEMABINDING
BEGIN
DECLARE @.val varchar(50)
SET @.val = @.Content.value('(/author/@.id)[1]','varchar(50)')
RETURN CASE ISNUMERIC(@.val) WHEN 1 THEN @.val ELSE NULL END
END
GO
ALTER TABLE AUTHORS ADD [Id]
AS [dbo].GetAuthorId([Content]) PERSISTED
GO
CREATE NONCLUSTERED INDEX IDX_AuthorId
ON AUTHORS([Id])
GO
DECLARE @.xml xml
SET @.xml = '
<authors>
<author id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>'
INSERT AUTHORS ([Content])
SELECT tmp.x.query('.')
FROM @.xml.nodes('/authors/author') tmp(x)
SELECT * FROM AUTHORS
Now you can join to the stored and indexed AUTHORS.Id column.
Marc
|||I think the gist of the discussion is that you basically have to shred the 2
documents to relational format and join using SQL. If, however, all of the
information were stored in a single document you could simulate an inner
join using a FLWOR expression.
1. If you put two "$var in path_expression" expressions in the for clause
separated by a comma, XQuery generates the cross join of the two path
expressions.
2. If you add a where clause to limit the results to those where your id
attribute is equal in both path expressions you have an inner join.
3. If you "and" another predicate to the where clause limiting the results
to those that contain the word "XQuery", you get author #2, "Chris".
In the example below I've combined your documents, removed the invalid "@."
character from the attribute names, and entitised the apostrophe in your
data. The FLWOR expression that follows generates the results. Of course all
this is dependent on the two documents being combined into a single doc.
DECLARE @.xml XML;
SELECT @.xml = '<authors>
<author id="1">
<name>Tom</name>
<tel>001-909-1129</tel>
</author>
<author id="2">
<name>Chris</name>
<tel>001-871-1341</tel>
</author>
<author id="3">
<name>Jack</name>
<tel>001-452-8721</tel>
</author>
</authors>
<catalog>
<book>
<title>SQL Server for Dummies</title>
<author id="1">Tom</author>
</book>
<book>
<title>XQuery for Dummies</title>
<author id="2">Chris</author>
</book>
<book>
<title>It's All About XML</title>
<author id="3">Jack</author>
</book>
</catalog>';
SELECT @.xml.query('for $a in /authors/author, $b in /catalog/book
where $a/@.id = $b/author/@.id
and $b/title[contains(., "XQuery")]
return $a');
<Paolo.sembung@.gmail.com> wrote in message
news:060ec030-adba-4ed3-bb99-c24e75fdee34@.y5g2000hsf.googlegroups.com...
> Hi,
> Suppose I have to XML documents. The first one contains a list of
> authors and the second one contains a list of books. For example,
> author.xml
> --
> <authors>
> <author @.id="1">
> <name>Tom</name>
> <tel>001-909-1129</tel>
> </author>
> <author @.id="2">
> <name>Chris</name>
> <tel>001-871-1341</tel>
> </author>
> <author @.id="3">
> <name>Jack</name>
> <tel>001-452-8721</tel>
> </author>
> </authors>
> book.xml
> --
> <catalog>
> <book>
> <title>SQL Server for Dummies</title>
> <author @.id="1">Tom</author>
> </book>
> <book>
> <title>XQuery for Dummies</title>
> <author @.id="2">Chris</author>
> </book>
> <book>
> <title>It's All About XML</title>
> <author @.id="3">Jack</author>
> </book>
> </catalog>
> Suppose I store "author.xml" as xml data type in the AUTHOR table and
> "book.xml" as xml data type in the BOOK table. I would like to get the
> information of authors who have written a book with title containing
> "XQuery".
> How can I write XQuery in SQL Server 2005 ?
> Thanks