We have a dozen or so custom PageBuilder Wireframe Templates used by thousands of content pages. We want to migrate all of our content from the old Wireframe templates to new Wireframe Templates. I initially thought about doing it by manually updating the content database via SQL but I'm not sure if that's a safe way to do it. Plus that involves some tricky SQL with encoded-xml-embedded-in-xml in text fields. Real ugly stuff.
Does anyone know the best way to do this?
A little advice first...
Thousands of wireframes seems like a lot. That's not a doubt of your word, rather a statement that most Ektron customers don't need that many PageBuilder-based pages. If you're using PageBuilder to create regular content pages (HTML or Smart Form) such as press releases, blog posts, or similar, then you may want to re-think that strategy. PageBuilder is difficult for most authors and for pages that don't require a lot of layout changes it can cause a lot of overhead in authoring as well as difficulty in migration (ahem...). Food for thought. Might be best to migrate what's in your Widgets into Smart Forms, use the ContentType approach, and developer plain old .NET WebForms templates.
Now...It's been a while since I've worked deeply in some of this. I also don't have any local instances of Ektron to play with any more, so please bear with me. Most of what I'm going to relay here is dredging up knowledge that's been unused for >2 years.
As you say, PageBuilder is represented as XML within the database. In addition, the Ektron DB is pretty large and complex. Doing both manipulations will be challenging as well as risky, the latter in particular on the SQL side. So if there were a way to go about things while strictly manipulating XML and avoid the SQL part, then that would be my first goal.
Something else to note is that PageBuilder is just content. Meaning you may be able to get access to the XML data using the standard Content API (whichever is appropriate for your version of Ektron, which you don't state above...). What I don't know is whether you would be able to create a new page as a PageBuilder Layout. Worth a shot, though.
There's also a set of undocumented APIs/Methods just for PageBuilder that you may be able to make use of. You can find the PageBuilder functionality within the workarea as User Controls - the code behind for those is not compiled. I actually used that information as reference material when I built the first prototype for PageBuilder Bootstrap support (eventually pre-released in 8.7 and reengineered for 9.0). Those controls should show you a bit more about how PageBuilder works and might help you in creating widget instances and assignments.