Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Lab 51 – Querying an RDF document using TSQL and XQuery

Feb 16 2010 3:36PM by Jacob Sebastian   

This installment of XQuery Labs presents a script that reads information from an RDF document using TSQL and XQuery.


One of my friends approached me recently with a request to write a TSQL query that reads information from an RDF document. Here is the sample XML document he wanted to process.


<RDF xmlns:r="http://www.w3.org/TR/RDF/"
      xmlns:d="http://purl.org/dc/elements/1. 0/"
      xmlns="http://dmoz.org/rdf/">
  <Topic r:id="Top/World/Afrikaans/Besigheid">
    <catid>724829</catid>
    <link r:resource="http://www.videos-sa.com" />
    <link r:resource="http://besigheidcenturion.co.za/bc/" />
  </Topic>
  <ExternalPage about="http://www.videos-sa.com">
    <d:Title>Kobus Petzer Videoproduksies</d:Title>
    <d:Description>Vervaardiging van...</d:Description>
    <topic>Top/World/Afrikaans/Besigheid</topic>
  </ExternalPage>
  <ExternalPage about="http://besigheidcenturion.co.za/bc/">
    <d:Title>Besigheid Centurion</d:Title>
    <d:Description>Sakeportaal vir ...</d:Description>
    <topic>Top/World/Afrikaans/Besigheid</topic>
  </ExternalPage>
  <ExternalPage about="http://besigheidcenturion.co.za/bc/">
    <d:Title>Besigheid Centurion</d:Title>
    <d:Description>Sakeportaal vir ...</d:Description>
    <topic>Top/World/Afrikaans/Besigheid</topic>
  </ExternalPage>
</RDF>

The challenge is to read the resource names from the Link element and join it with the about attribute in the ExternalPage element. Here is the output required from the above XML document.

/*
Resource           About            Title         CatID 
------------------ ---------------- ------------- ------
http://www.videos- http://www.video Kobus Petzer  724829
http://besigheidce http://besigheid Besigheid Cen 724829
http://besigheidce http://besigheid Besigheid Cen 724829
*/      

While writing the query to get the above result is pretty much straight forward, many people find it difficult because of the namespace declarations present in the XML document.

Here is the TSQL code that queries the above XML document and produces the required output.

DECLARE @x XML
SELECT @x = '
<RDF xmlns:r="http://www.w3.org/TR/RDF/"
      xmlns:d="http://purl.org/dc/elements/1. 0/"
      xmlns="http://dmoz.org/rdf/">
  <Topic r:id="Top/World/Afrikaans/Besigheid">
    <catid>724829</catid>
    <link r:resource="http://www.videos-sa.com" />
    <link r:resource="http://besigheidcenturion.co.za/bc/" />
  </Topic>
  <ExternalPage about="http://www.videos-sa.com">
    <d:Title>Kobus Petzer Videoproduksies</d:Title>
    <d:Description>Vervaardiging van...</d:Description>
    <topic>Top/World/Afrikaans/Besigheid</topic>
  </ExternalPage>
  <ExternalPage about="http://besigheidcenturion.co.za/bc/">
    <d:Title>Besigheid Centurion</d:Title>
    <d:Description>Sakeportaal vir ...</d:Description>
    <topic>Top/World/Afrikaans/Besigheid</topic>
  </ExternalPage>
  <ExternalPage about="http://besigheidcenturion.co.za/bc/">
    <d:Title>Besigheid Centurion</d:Title>
    <d:Description>Sakeportaal vir ...</d:Description>
    <topic>Top/World/Afrikaans/Besigheid</topic>
  </ExternalPage>
</RDF>'

;WITH XMLNAMESPACES(
      'http://www.w3.org/TR/RDF/' as r,
      'http://purl.org/dc/elements/1. 0/' as d,
      default 'http://dmoz.org/rdf/'     
)
SELECT
      y.value('@r:resource[1]','VARCHAR(100)') AS Resource,
      z.value('@about','VARCHAR(100)') AS About,
      z.value('d:Title[1]','VARCHAR(100)') AS Title,
      x.value('catid[1]','VARCHAR(100)') AS CatID
FROM @x.nodes('RDF/Topic') a(x)
CROSS APPLY x.nodes('link') b(y)
CROSS APPLY  @x.nodes('RDF/ExternalPage') c(z)
WHERE y.value('@r:resource[1]','VARCHAR(100)') = 
	z.value('@about','VARCHAR(100)')
      
/*
Resource           About            Title         CatID 
------------------ ---------------- ------------- ------
http://www.videos- http://www.video Kobus Petzer  724829
http://besigheidce http://besigheid Besigheid Cen 724829
http://besigheidce http://besigheid Besigheid Cen 724829
*/      

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

Tags: XQuery-Labs, XML, XQuery, XQuery Functions, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,


Jacob Sebastian
1 · 100% · 32235
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Hi Jacob...

    Good XML post.

    The query could be re-worked to insert another CROSS APPLY to get the result columns and then you could use those column aliases in the WHERE clause. This may make the query clearer AND it involves less typing and duplication.

    (The code may not be indented here as well as I'd like):

    ;WITH XMLNAMESPACES( 'http://www.w3.org/TR/RDF/' as r, 'http://purl.org/dc/elements/1. 0/' as d, default 'http://dmoz.org/rdf/'
    ) SELECT Resource, About, Title, CatID FROM @x.nodes('RDF/Topic') a(x) CROSS APPLY x.nodes('link') b(y) CROSS APPLY @x.nodes('RDF/ExternalPage') c(z) CROSS APPLY (SELECT y.value('@r:resource[1]','VARCHAR(100)') AS Resource, z.value('@about','VARCHAR(100)') AS About, z.value('d:Title[1]','VARCHAR(100)') AS Title, x.value('catid[1]','VARCHAR(100)') AS CatID) F WHERE Resource=About

    commented on Feb 16 2010 6:59PM
    Brad Schulz
    107 · 2% · 483
  • Thanks Brad, I made another post showing your method. http://beyondrelational.com/blogs/jacob/archive/2010/02/25/XQuery-Lab-52-A-More-Intuitive-way-of-reading-an-RDF-document.aspx

    commented on Feb 25 2010 4:16AM
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]