XQuery to generate DROP TABLE IF EXISTS + INSERT INTO statements

Question

I'm using XQuery to go over a bunch of XML files and extract indexed terms and turn them into SQL insert statements. This is very straightforward:

xquery version "3.0";
declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";
declare option output:method "text";
for $index in collection(/db/letters/)//index/text()
return 
    concat("INSERT INTO `indices` SET index='", $index, "';")

This generates statements like:

INSERT INTO `indices` SET index='foo';
INSERT INTO `indices` SET index='bar';

which is all fine and dandy. But I would like to output some text once before and once after all those statements, namely, first:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `indices`
-- ----------------------------
DROP TABLE IF EXISTS `indices`;
CREATE TABLE `indices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `norm` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

and, at the end,

SET FOREIGN_KEY_CHECKS = 1;"

In PHP this would be a non-brainer, but in XQuery it's much more difficult, especially for non-advanced users like myself.

The FLOWR expressions seem very easy and logical when outputting XML, but I can't figure out how to concat the cumulative return with two other strings.

Any pointers will be warmly appreciated.

score 2 · Accepted Answer · answered Oct 22 '16 at 05:12

I use XQuery to do this frequently. It is sometimes helpful to remember that XQuery is really all about sequences and that FLOWR expressions are just one way to generate a sequence. There are several ways to achieve what you are attempting. Examples follow.

xquery version "3.0";
declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";
declare option output:method "text";
let $newline := '&#xa;'
let $beginning :=
    ( "your first line",
      "your second line",
      "your third line",
      ...)
let $middle :=
   for    $index in collection(/db/letters/)//index/text()
   return concat("INSERT INTO `indices` SET index='", $index, "';")
let $end := ( "your first end line", "your second end line", ... )
return
   string-join( ($beginning, $middle, $end), $newline )

or similar:

let $newline := '&#xa;'
let $lines :=
(
    ( "your first line",
      "your second line",
      "your third line",
      ...),
    (for    $index in collection(/db/letters/)//index/text()
     return concat("INSERT INTO `indices` SET index='", $index, "';")
    ),
    ( "your first end line", "your second end line", ... )
)
return string-join( $lines, $newline )

or some processors will let you create text nodes using syntax such that your query can produce a sequence of text nodes with a newline at the end of each. Though, please note the sequence inside the text { } element may be outputted separated by tabs so in the example below there may be a tab before the newline. That can be overcome by concat()'ing everything in the text { } node.

let $newline := '&#xa;'
return
(  text{ 'your first line', $newline },
   text{ 'your second line', $newline },
   text{ '...', $newline },
   (for   $index in collection(/db/letters/)//index/text()
    return text{concat("INSERT INTO `indices` SET index='", $index, "';"),$newline}
   ),
   text {"your first end line", $newline },
   text {"your second end line", $newline },
   ...
)

XQuery to generate DROP TABLE IF EXISTS + INSERT INTO statements

1 Answers1