SELECTALL_XML(1p)

💖 SELECTALL_XML 💖

okay bestie, gather round 🫶 this is SELECTALL_XML(1p), a lil gem from the User Contributed Perl Documentation collection, and it's about to become your new fave. no cap.

---

✨ NAME ✨

stag-selectall_xml — query every single element from a database and get XML back out. it's giving database-to-XML pipeline realness. 💅

---

🌸 SYNOPSIS 🌸

stag-selectall_xml [-d <dbi>] [-f file of sql] [-nesting|n <nesting>] SQL

---

🦋 DESCRIPTION 🦋

so here's the tea ☕ — this script queries a database using either SQL that you (the user) handed it, or an SQL template. then it takes those query results and turns them into XML via the DBIx::DBStag module. obsessed. 🔥

the nesting of that XML? you control it with the DBStag SQL extension USE NESTING.... iconic behavior honestly.

🎀 EXAMPLES 🎀

stag-selectall_xml -d "dbi:Pg:dbname=mydb;host=localhost"\
      "SELECT * FROM a NATURAL JOIN b"

look at that backslash line continuation go ✨

💜 TEMPLATES 💜

instead of typing out the whole entire SQL, you can use a parameterized SQL template (aka a canned query). so much chiller.

for example:

stag-selectall_xml -d genedb /genedb-gene gene_symbol=Adh

or:

stag-selectall_xml -d genedb /genedb-gene Adh

or:

stag-selectall_xml -d genedb /genedb-gene gene_symbol@=Adh,dpp,bam,indy

so how do you signal "hey this is a template"? the syntactic shorthand is a slash in front of the template name — in this case the template is called genedb-gene. you can also just use the -t option if slashes aren't your vibe.

all the remaining arguments get passed in as SQL template parameters, okay? and they can come in as either name=value pairs, or as a simple list of arguments that get passed into the template in order. either way works, do what feels right 🫶

to use templates, you should have the environment variable DBSTAG_TEMPLATE_DIRS set. see DBIx::DBStag for details. 👑

🌈 LISTING AVAILABLE TEMPLATES FOR A DB 🌈

stag-selectall_xml -d mydb -h

⭐ LISTING VARIABLES FOR A TEMPLATE ⭐

stag-selectall_xml /genedb-gene -h

---

💀 ENVIRONMENT VARIABLES 💀

• DBSTAG_DBIMAP_FILE — a file containing configuration details for local databases. your local database lore lives here 📁
• DBSTAG_TEMPLATE_DIRS — list of directories (separated by :s) to be searched when templates are requested. yes, colons. plural. 💅

---

👑 COMMAND LINE ARGUMENTS 👑

• -h|help — shows this page if no other arguments are given.

if a template is specified, gives template details.

if a db is specified, lists templates for that db.

use in conjunction with -v for full descriptions. (iykyk 🔍)

• -d|dbname DBNAME — this is either a full DBI locator string (eg dbi:Pg:dbname=mydb;host=localhost) or it can also be a shortened "nickname", which is then looked up in the file pointed at by the environment variable DBSTAG_DBIMAP_FILE. nicknames are lowkey iconic ngl.
• -u|user USER — database user identity. who are you bestie 🫶
• -p|password PASS — database password. shhh 🤫
• -f|file SQLFILE — this is a path to a file containing SQL that will be executed, as an alternative to writing the SQL on the command line. write it in a file, serve it fresh 📄
• -n|nesting NESTING-EXPRESSIONS — a bracketed expression indicating how to the resulting objects/XML should be nested. see DBIx::DBStag for details. (yes, the original says "how to the resulting" — we keep the facts EXACTLY as they are, periodt. 💅)
• -t|template TEMPLATE-NAME — the name of a template; see above. ⬆️
• -wh|where WHERE-CLAUSE — used to override the WHERE clause of the query; useful for combining with templates.

you can append to an existing where clause by using the prefix +. little plus sign, big power. ✨

• -s|select SELECT-COLS — used to override the SELECT clause of the query; useful for combining with templates.
• -rows — sometimes it is preferable to return the results as a table rather than xml or a similar nested structure. specifying -rows will fetch a table, one line per row, and columns separated by tabs. tab-separated realness 🍓
• -pre SQL — a piece of SQL is that is executed immediately before the main query; e.g.:

-pre "SET search_path=myschema,public"

• -o|out FILE — a file to output the results to. redirection but make it fancy 🎀
• -w|writer WRITER — writer class; can be any perl class, or one of these:
  • xml [default]
  • sxpr — lisp S-Expressions
  • itext — indented text
• -color — shows results in color (sxpr and itext only). 🌈 aesthetic output alert 🌈
• -show — will show the parse of the SQL statement. see the SQL's inner thoughts 🧠

---

and that's the whole page bestie!! you ate that 🫶✨

this masterpiece was brought to you by perl v5.36.0, dated 2022-10-14, from the SELECTALL_XML(1p) girlies themselves. mwah 💋 see you in the next manpage, no cap. 💖