• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: The XML Composition Conundrum of XMLNAMESPACES or XMLATTRIBUTES

    April 3, 2017 Michael Sansoterra

    Hey, Mike:

    After reading the IT Jungle Guru tips on DB2 XML Composition (see Related Stories below), I’ve been able to build XML from relational data. I have everything working but now I need to enclose all data in a “Document” tag after the XML declaration like the example here:

    <?xml version="1.0" encoding="utf-8"?>
    <Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="http://MyCompany.com" 
    xsi:schemaLocation="http://MyCompany.com validate.xsd">
    

    I tried using XMLNAMESPACES but can’t get it to produce the “xsi:schemaLocation” tag. How can I construct the document tag I need?

    –N.C.

     

    The problem here is that the xsi:schemaLocation in your example is not a namespace, but an attribute (as identified in articles by w3schools.com and microsoft.com). Therefore, you should use the XMLATTRIBUTES function rather than XMLNAMESPACES to incorporate it into your XML.

    Let me illustrate how to build all of this using the DB2 XML functions. To keep it easy to understand, the following SQL builds XML from basic relational data using the XMLELEMENT function:

    CREATE TABLE MyTable (Contents VARCHAR(12));
    INSERT INTO MyTable VALUES ('NADA');
    SELECT XMLELEMENT(NAME "MyData", Contents) AS MyXML
      FROM MyTable;
    

    The query returns this simple XML:

    <MyData>NADA</MyData>
    

    Now, we’ll embed the above query in a common table expression called BUILD_XML and then incorporate the query’s XML within the desired &lt;Document&gt; root element using the XMLELEMENT and XMLSERIALIZE functions:

    WITH BUILD_XML (MY_XML) AS  (
    SELECT XMLELEMENT(NAME "MyData", Contents) AS MyXML
      FROM MyTable
    )
    SELECT XMLSERIALIZE(
    XMLELEMENT(NAME "Document",MY_XML)
    AS CLOB(1M) INCLUDING XMLDECLARATION) 
      FROM BUILD_XML
    

    XMLELEMENT is used to build the Document root element (with the MY_XML column supplying the element’s child nodes) and the XMLSERIALIZE function adds the declaration:

    <?xml version="1.0" encoding="UTF-8"?>
    <Document>
    <MyData>NADA</MyData>
    </Document>
    

    Now all that is left to do is to add the two namespaces:

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="http://MyCompany.com" 
    

    And then the troublesome schemaLocation attribute to the Document element:

    xsi:schemaLocation="http://MyCompany.com validate.xsd"
    

    Both of these can be added to the XML by specifying the XMLNAMESPACES and XMLATTRIBUTES functions as parameters to the XMLELEMENT function (for the Document element):

    WITH BUILD_XML (MY_XML) AS (
    SELECT XMLELEMENT(NAME "MyData", Contents) AS MyXML
      FROM MyTable
    )
    SELECT XMLSERIALIZE(
    XMLELEMENT(NAME "Document",
    XMLNAMESPACES(
    'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
    DEFAULT 'http://MyCompany.com'
    ),
    XMLATTRIBUTES(
    'http://MyCompany.com validate.xsd' AS "xsi:schemaLocation"
    ),
    MY_XML)
    AS CLOB(1M) INCLUDING XMLDECLARATION) 
      FROM BUILD_XML
    

    The result is:

    <?xml version="1.0" encoding="UTF-8"?>
    <Document 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="http://MyCompany.com" 
    xsi:schemaLocation="http://MyCompany.com validate.xsd">
    <MyData>NADA</MyData>
    </Document>
    

    And that’s all it takes to build the required XML. XML has so many facets, that when you encounter something unfamiliar, sometimes it’s easier to search for it online (such as schemaLocation) to make sure you’ve correctly identified what it is (attribute, namespace, element, CDATA, etc.). Thereafter you can pick the appropriate DB2 for i function to assist with the XML composition.

    RELATED STORIES

    Composing An XML Document From Relational Data, Part 1

    Composing An XML Document From Relational Data: Part 2

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: DB2, Four Hundred Guru, Guru, XML, XMLATTRIBUTES, XMLNAMESPACES

    Sponsored by
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Mad Dog 21/21: iBird In The Hand Or iToo In The Bush Time To Get Your 2FA On, IBM i Admins

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 22

This Issue Sponsored By

  • ProData Computer Services
  • Quadrant Software
  • COMMON
  • WorksRight Software
  • Northeast User Groups Conference

Table of Contents

  • MSPs Get Monthly IBM i Pricing; Why Not Everyone?
  • Time To Get Your 2FA On, IBM i Admins
  • Guru: The XML Composition Conundrum of XMLNAMESPACES or XMLATTRIBUTES
  • Mad Dog 21/21: iBird In The Hand Or iToo In The Bush
  • SystemObjects SmartPad4i Mobilizes RDi

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM
  • Critical Security Vulnerability In PowerVM Hypervisor
  • IBM Power: Hosted On-Premises Or In The Cloud?
  • Guru: Watch Out For This Pitfall When Working With Integer Columns
  • As I See It: Bob-the-Bot
  • IBM i PTF Guide, Volume 25, Number 21

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle