Mysql – ny scenario where a exported (.sql) database dump could be bigger in size, than the database itself

I have dealt with questions like this in both directions

• Mar 20, 2013 : How can I determine the size of a dump file prior to dumping?
• May 21, 2012 : How large will a MySQL database be relative to the dump file?

Your question is a little different because you did not give anything about the target MySQL Instance (what Storage Engine will be used, which columns are TEXT/BLOB, what indexing is needed for your data, etc.)

IMHO the most straightforward way I can think of is the following

METHOD #1

Write some Perl/Python script to sum the length of all XML tag delimiters. Subtract the sum you get from the length of the file. That would essentially be the size of a CSV file less delimiters. This would be the bytes of the raw data.

METHOD #2

Do METHOD #1 and also take the count of all XML tag delimiters and add that count to the result of METHOD #1. That would like adding commas back into a CSV file. This make be closer in size to an actual CSV should you print the output to such a file.

You can use a variant of the technique described in Using merge..output to get mapping between source.id and target.id and by Adam Machanic in Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE.

You use merge and capture the generated ID with the XML fragment that belongs to that ID in a table variable and then use that table variable when adding rows to the child tables.

declare @C table
(
  CONSTRUCTION_ID int primary key,
  PROJECT xml
);

merge CONSTRUCTION as T
using (
      select T.X.value('(constructionName/text())[1]', 'varchar(30)') as CONSTRUCTIONNAME,
             T.X.value('(constructionType/text())[1]', 'varchar(30)') as CONSTRUCTIONTYPE,
             T.X.query('project') as PROJECT
      from @xml.nodes('/root/construction') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTIONNAME, CONSTRUCTIONTYPE) 
  values (S.CONSTRUCTIONNAME, S.CONSTRUCTIONTYPE)
output inserted.CONSTRUCTION_ID, S.PROJECT into @C;

declare @P table
(
  PROJECT_ID int primary key,
  BUSINESSPARTNERS XML
);

merge PROJECT as T
using (
      select C.CONSTRUCTION_ID,
             T.X.value('(projectname/text())[1]', 'varchar(30)') as PROJECTNAME,
             T.X.query('businesspartners') as BUSINESSPARTNERS
      from @C as C
        cross apply C.PROJECT.nodes('/project') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTION_ID, PROJECTNAME)
  values(S.CONSTRUCTION_ID, S.PROJECTNAME)
output inserted.PROJECT_ID, S.BUSINESSPARTNERS into @P;

insert into BUSINESSPARTNERS(PROJECT_ID, PARTNERNAME)
select P.PROJECT_ID,
       T.X.value('text()[1]', 'varchar(30)')
from @P as P
  cross apply P.BUSINESSPARTNERS.nodes('/businesspartners/partnername') as T(X);

SQL Fiddle