CODEWITHSUNDEEP
sqlsql-serverdatabase
Umberto De Luca
Umberto De Luca

Reputation: 37

Query XML columns over multiple tables in SQL Server database

I have a database which has two tables, namely:

Boats

user_id|boat_id|sails
1      1      <sails><id135986><sailinfo>A2</sailinfo><sailtype>Main</sailtype></id135986><id185764><sailinfo>#3</sailinfo><sailtype>Jib</sailtype></id185764></sails>
1      2      <sails><id10230><sailinfo>A2</sailinfo><sailinfo>Main</sailinfo></id10230><id20000><sailinfo>#5</sailinfo><sailinfo>Genoa</sailinfo></id20000></sails>
2      3      <sails><id43567><sailinfo>A2</sailinfo><sailinfo>Main</sailinfo></id43567><id24503><sailinfo>#5</sailinfo><sailinfo>Genoa</sailinfo></id24503></sails>

Records

user_id|boat_id|location            |sails
1       1       San Francisco        <sails><id135986>id135986</id135986><id185764>id185764</id185764></sails>
1       2       Chicago              <sails><id10230>id10230</id10230></sails>
1       2       Chicago              <sails><id20000>id20000</id20000></sails>
1       2       New York             <sails><id10230>id10230</id10230><id20000>id20000</id20000></sails>
2       3       Bermuda              <sails><id43567>id43567</id43567></sails>

The idea behind this structure is that if the records "sailinfo" and "sailtype" are updated in the table Boats, the Records table is unaffected because it is linked to the other one by the id tag.

Now, I have an application where the user can choose a sailinfo (e.g. A2) and based on this input a query should be generated to retrieve the location where that particular sailinfo has been used. The results should be dispayed in tabular format where the first column ("Location") contains the relevant locations and the second column should have the "sailtype" associated with "A2" as header and A2 as entry. For instance, suppose the user with user_id=2 inputs "A2" for boat_id=2, this is what should be returned:

location | Jib
Chicago    A2
New York   A2

I have tried using the following SQL statement but it didn't work:

DECLARE @boat_id VARCHAR(50);
SET @boat_id = (SELECT Boats.sails.value('local-name(/sails[1]/*[1])','nvarchar(50)') 
                FROM Boats 
                WHERE boat_id = 88 
                  AND Boats.sails.value('(/sails/*/sailinfo)[1]', 'nvarchar(50)') = 'A2');

DECLARE @record_id varchar(50);
SET @record_id = (SELECT Records.sails.value('local-name(/sails[1]/*[1])', 'nvarchar(50)') 
                  FROM Records 
                  WHERE boat_id = 2 
                    AND Records.sails.value('local-name(/sails[1]/*[1])', 'nvarchar(50)') = @boat_id );

SELECT 
    [location], Boats.sails.value('(/sails/id10230/sailinfo)[1]', 'nvarchar(50)') AS 'Jib' 
FROM 
    Boats 
FULL JOIN 
    Records ON Records.sails.value('local-name(/sails[1]/*[1])', 'nvarchar(50)') = @boat_id 
WHERE 
    Boats.boat_id = 2;

Of course since I am picking from table Boats the above returns NULL for "location" and just a single row for "Jib":

Sample query result

I hope the above description is clear enough.

Your help would be greatly appreciated!

Upvotes: 0

Views: 581

Answers (1)

Slava Murygin
Slava Murygin

Reputation: 1955

That statement does not work at first because in your data you do non have 'boat_id = 88' and then you do not have 'id28108'

Code below returns what you want:

SELECT 
    [location], Boats.sails.value('(/sails/id10230/sailinfo)[1]', 'nvarchar(50)') AS 'Jib'
FROM 
    #tbl_Boats as Boats 
FULL JOIN 
    #tbl_Records as Records 
ON Records.sails.value('local-name(/sails[1]/*[1])', 'nvarchar(50)') 
    = Boats.sails.value('local-name(/sails[1]/*[1])','nvarchar(50)') 
WHERE Records.sails.value('(/sails/id10230)[1]', 'nvarchar(50)') is not null;

P.S. Something looks wrong. Try to store some values in columns, outside of XML. That will work much faster and will be easier to understand/troubleshoot.

Upvotes: 0

Related Questions