XSS attacks are based upon the fact that input becomes output to the end-user's browser. The most common attack is basically a PHP site containing
<?php
echo $_GET["message"];
?>
You would then pass this URL a parameter containing the javascript code.
If you want to set this up, create a something.php
file on your web server, input the above code into it and then access http://your-server/something.php?message=<script>alert('XSS');</script>
in your browser. It should then display a popup containing XSS.
In a static HTML page, this is not possible, as it only generates content based on the server's static HTML code. XSS needs user-supplied code inclusion. The exclusion is, if the html loads a vulnerable javascript code, that allows user-supplied input.
The primary point of XSS is that an attacker wants to include HIS code to YOUR website, without actually hacking the web server. This is only possible with server-side programming languages, which output something, the attacker previously put into the server.
Basically the hacker wants the HTML to look to the browser like this
<html><body>Foo<script>alert('XSS');</script></body></html>
instead of
<html><body>Foo</body></html>
Think for example of a forum or comments on an article. The user should be allowed to leave his remarks. The server needs to save this and present the same comment to other users. If the comment itself contains javascript code and the server program does not mitigate this, it would output the same code as part of the comment block.
As to when this is loaded in the browser, this is not easy to answer. It depends on where the hacker's code is included in the page body or the subsequently loaded javascript files. If it is included as a javascript tag in the main HTML, it will be loaded after the DOM load was completed.
Especially as mentioned before, the XSS vulnerability could also happen inside a javascript file (which takes user input, for example a URL and loads it). In this case it is not possible to say, when exactly the code will execute. You could bind this javascript code to a button, a textbox onblur() event or a timer.
EDIT:
Elaborating on the javascript attack, here's what you would put into your server file
<html><body>
<script type="text/javascript">
var queryDict = {};
location.search.substr(1).split("&").forEach(function(item) {queryDict[item.split("=")[0]] = item.split("=")[1]});
document.write(decodeURIComponent(queryDict["message"]));
</script>
</body></html>
The first two lines, bascially take all GET parameters and split them into an array (taken from here). The third line then simply outputs this into the HTML page. Of course this could also be done by any other DOM manipulation from the executed code.