SELECT
tblMain.MainID,
tblMain.MainText,
tblSub.SubID,
tblSub.Sub
FROM
tblMain
LEFT OUTER JOIN
tblSub
ON
tblMain.MainID = tblSub.MainID;
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2010-06-18T16:45:51">
<tblMain>
<MainID>1</MainID>
<MainText>First</MainText>
</tblMain>
<tblMain>
<MainID>2</MainID>
<MainText>Second</MainText>
</tblMain>
<tblMain>
<MainID>3</MainID>
<MainText>Third</MainText>
</tblMain>
<tblMain>
<MainID>4</MainID>
<MainText>Fourth</MainText>
</tblMain>
<tblMain>
<MainID>5</MainID>
<MainText>Fifth</MainText>
</tblMain>
<tblMain>
<MainID>6</MainID>
<MainText>Sixth</MainText>
</tblMain>
<tblMain>
<MainID>7</MainID>
<MainText>Seventh</MainText>
</tblMain>
<tblMain>
<MainID>8</MainID>
<MainText>Eighth</MainText>
</tblMain>
<tblMain>
<MainID>9</MainID>
<MainText>Ninth</MainText>
</tblMain>
<tblMain>
<MainID>10</MainID>
<MainText>Tenth</MainText>
</tblMain>
</dataroot>
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2010-06-18T16:46:11">
<tblSub>
<SubID>1</SubID>
<MainID>1</MainID>
<Sub>1 First Sub</Sub>
</tblSub>
<tblSub>
<SubID>2</SubID>
<MainID>1</MainID>
<Sub>2 First Sub</Sub>
</tblSub>
<tblSub>
<SubID>3</SubID>
<MainID>4</MainID>
<Sub>1 Fourth Sub</Sub>
</tblSub>
<tblSub>
<SubID>4</SubID>
<MainID>8</MainID>
<Sub>1 Eighth Sub</Sub>
</tblSub>
</dataroot>
xquery version "1.0";
<results>
{
for $main in doc("C:\Xquery Outer Joins\tblMain.xml")/dataroot/tblMain
return
<row>
<MainID>{data($main/MainID)}</MainID>
<MainText>{data($main/MainText)}</MainText>
{for $sub in doc("C:\Xquery Outer Joins\tblSub.xml")/dataroot/tblSub[MainID=$main/MainID]
return
<Sub>{data($sub/Sub)}</Sub>
}
</row>
}
</results>
<results>
<row>
<MainID>1</MainID>
<MainText>First</MainText>
<Sub>1 First Sub</Sub>
<Sub>2 First Sub</Sub>
</row>
<row>
<MainID>2</MainID>
<MainText>Second</MainText>
</row>
<row>
<MainID>3</MainID>
<MainText>Third</MainText>
</row>
<row>
<MainID>4</MainID>
<MainText>Fourth</MainText>
<Sub>1 Fourth Sub</Sub>
</row>
<row>
<MainID>5</MainID>
<MainText>Fifth</MainText>
</row>
<row>
<MainID>6</MainID>
<MainText>Sixth</MainText>
</row>
<row>
<MainID>7</MainID>
<MainText>Seventh</MainText>
</row>
<row>
<MainID>8</MainID>
<MainText>Eighth</MainText>
<Sub>1 Eighth Sub</Sub>
</row>
<row>
<MainID>9</MainID>
<MainText>Ninth</MainText>
</row>
<row>
<MainID>10</MainID>
<MainText>Tenth</MainText>
</row>
</results>
xquery version "1.0";
<results>
{
(: Final variable :)
let $result :=
(
(: First variable :)
let $unmatched :=
(for $main1 in doc("C:\Xquery Outer Joins\tblMain.xml")/dataroot/tblMain
where empty(doc("C:\Xquery Outer Joins\tblSub.xml")/dataroot/tblSub[MainID = $main1/MainID])
return
<row>
<ID>{data($main1/MainID)}</ID>
<Main>{data($main1/MainText)}</Main>
<Sub>{'-'}</Sub>
</row> )
(: Second variable :)
let $matched :=
(for $main in doc("C:\Xquery Outer Joins\tblMain.xml")/dataroot/tblMain
for $sub in doc("C:\Xquery Outer Joins\tblSub.xml")/dataroot/tblSub[MainID = $main/MainID]
return
<row>
<ID>{data($main/MainID)}</ID>
<Main>{data($main/MainText)}</Main>
<Sub>{data($sub/Sub)}</Sub>
</row>
)
return
$unmatched|$matched
)
(: sort and output :)
for $sorted in $result
order by $sorted/ID
return $sorted
}
</results>
(: First variable :)
let $unmatched :=
(for $main1 in doc("C:\Xquery Outer Joins\tblMain.xml")/dataroot/tblMain
where empty(doc("C:\Xquery Outer Joins\tblSub.xml")/dataroot/tblSub[MainID = $main1/MainID])
return
<row>
<ID>{data($main1/MainID)}</ID>
<Main>{data($main1/MainText)}</Main>
<Sub>{'-'}</Sub>
</row>
)
(: Second variable :)
let $matched :=
(for $main in doc("C:\Xquery Outer Joins\tblMain.xml")/dataroot/tblMain
for $sub in doc("C:\Xquery Outer Joins\tblSub.xml")/dataroot/tblSub[MainID = $main/MainID]
return
<row>
<ID>{data($main/MainID)}</ID>
<Main>{data($main/MainText)}</Main>
<Sub>{data($sub/Sub)}</Sub>
</row>
)
let $result :=
(
(
Build $unmatched
)
(
Build $matched
)
return
$unmatched|$matched
)
(: sort and output :)
for $sorted in $result
order by $sorted/ID
return $sorted
<results>
<row>
<ID>1</ID>
<Main>First</Main>
<Sub>1 First Sub</Sub>
</row>
<row>
<ID>1</ID>
<Main>First</Main>
<Sub>2 First Sub</Sub>
</row>
<row>
<ID>10</ID>
<Main>Tenth</Main>
<Sub>-</Sub>
</row>
<row>
<ID>2</ID>
<Main>Second</Main>
<Sub>-</Sub>
</row>
<row>
<ID>3</ID>
<Main>Third</Main>
<Sub>-</Sub>
</row>
<row>
<ID>4</ID>
<Main>Fourth</Main>
<Sub>1 Fourth Sub</Sub>
</row>
<row>
<ID>5</ID>
<Main>Fifth</Main>
<Sub>-</Sub>
</row>
<row>
<ID>6</ID>
<Main>Sixth</Main>
<Sub>-</Sub>
</row>
<row>
<ID>7</ID>
<Main>Seventh</Main>
<Sub>-</Sub>
</row>
<row>
<ID>8</ID>
<Main>Eighth</Main>
<Sub>1 Eighth Sub</Sub>
</row>
<row>
<ID>9</ID>
<Main>Ninth</Main>
<Sub>-</Sub>
</row>
</results>
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Author
Commented:First up, from David Carlisle is this:
Open in new window
Second up, from Martin Probst, is this simple solution:
Both of these illustrate some great lessons in XQuery. For me, they are a lesson in how I think in SQL, and can visualize SQL solutions, but still really struggle to do so with XQuery. However, I've been developing with XQuery for fifteen years and with XQuery for three months, so maybe that's not a surprise.
I've got to write queries based on what I've learned here that will operate on XML data representing millions of rows of relational data. I'll try all three approaches and check them for performance, then I'll post the results here.
Author
Commented:Open in new window