XML Inner Join with Table

[Ben]

Hi All,

I have an input XML which needs to make an inner join with table on a
specific field. I could not find the correct sysntax to do that. Here is
my sample;

DECLARE @XmlData xml
SET @XmlData='<Library>
<Subject name="ASP.NET">
<Book ID="1"><Author>Lakhan Pal Garg</Author><Title>ASP.NET
Tips</Title><Price>$100</Price></Book>
<Book ID="2"><Author>Lakhan Pal Garg</Author><Title>SQL Server
Tips</Title><Price>$90</Price></Book>
</Subject>
<Subject name="XML">
<Book ID="3"><Author>Peter</Author><Title>XSLT
Tutorial</Title><Price>$140</Price></Book>
<Book ID="4"><Author>Rihana</Author><Title>XML Parsing in SQL
Server</Title><Price>$120</Price></Book>
</Subject>
</Library>'

select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]
from @XmlData.nodes('/Library/Subject/Book') R(i)

If in the example above, I have Author names in the database, how would
i load only those that matched my Authors table?

Thanks in advance,
Ben

*** Sent via Developersdex http://www.developersdex.com ***

[Martin Honnen]

Assuming that table is named 'Authors' and has a column named 'Name' one
way is a follows:

select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]
from @XmlData.nodes('/Library/Subject/Book') R(i)

WHERE EXISTS(
SELECT NULL
FROM Authors A
WHERE A.Name = R.i.value('(Author)[1]', 'nvarchar(20)')
);

--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

[Martin Honnen]

Ben wrote:

> I have an input XML which needs to make an inner join with table on a
> specific field.

Or with a join:

select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]
from @XmlData.nodes('/Library/Subject/Book') R(i)

INNER JOIN Authors A ON R.i.value('(Author)[1]', 'nvarchar(20)') = A.Name

[Ben]

What if the join condition was outside of <Subject> but inside Library?

Thanks !

[Martin Honnen]

Ben wrote:
> What if the join condition was outside of <Subject> but inside Library?

Can you post a sample of the XML you have and what kind of condition you
want to enforce?

[lakhan]

Hi Use Join for

select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]
from @XmlData.nodes('/Library/Subject/Book') R(i)

And your Author Table and add condition for the result you want to
display...

For more specific detail you can revert back to me or post your query
here with details of author table:
http://lakhangarg.blogspot.com/2009/09/post-your-queries.html

Thanks & Regards
Lakhan pal Garg
Free Code Snippets
http://lakhangarg.blogspot.com

[Pal Garg@discussions.microsoft.com Lakhan Pal Garg]

Hi Ben-

Following Example will help you to solve your problem posted above:

DECLARE @TagXML XML
SET @TagXML='<CD TagID="762" Score="40" ClassCount="1" />
<CD TagID="803" Score="30" ClassCount="1" />
<CD TagID="9023" Score="40" ClassCount="1" />
<CD TagID="10563" Score="40" ClassCount="1" />'

SELECT Dic.TagID,Dic.TagName,Dic.OriginalTagName,Tag.Score,Tag.ClassCount
FROM Tbl_TagDictionary Dic INNER JOIN
(SELECT R.i.value('@TagID','INT')[TagID],R.i.value('@Score','float')[Score],
R.i.value('@ClassCount','int')[ClassCount]
FROM @TagXML.nodes('/CD')R(i)) Tag ON Tag.TagID=Dic.TagID

In this Example i need to match the recor on the basis of TagID. Similar way
you can use to get record according to the author table.

Thanks & Regards
Lakhan Pal Garg