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 ***

Ben wrote:
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/

Ben wrote:

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


--

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

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

Thanks !



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

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

--

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

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/...r-queries.html

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

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.Scor e,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

"Martin Honnen" wrote: