Convert nested XML via XQuery to flat xml

sql for xml nested elements
using the xml document books xml provided with this homework define the following queries in xquery
sql server xml select child nodes
convert xml column to table sql server
convert xml to table sql server
sql server xml query multiple nodes
sql query xml column element value
convert to xml in sql server

I've a nested piece of XML i want to insert in to SQL.

Import XML:

 <RECORD>
    <RECID>118810</RECID>
    <FIELD TYPE="C">
       <NAME>proj_code</NAME>
       <VALUE>118810</VALUE>
    </FIELD>
    <FIELD TYPE="C">
       <NAME>sub_nr</NAME>
       <VALUE>99900</VALUE>
    </FIELD>
    <FIELD TYPE="C">
       <NAME>proj_desc</NAME>
       <VALUE>Nagekomen kosten Oktober 2018</VALUE>
    </FIELD>
    <FIELD TYPE="N">
       <NAME>pro_stat</NAME>
       <VALUE>9</VALUE>
    </FIELD>
    <FIELD TYPE="C">
       <NAME>comment</NAME>
       <VALUE></VALUE>
    </FIELD>
</RECORD>

I want to convert via XQuery it into:

<RECORD>
    <RECID>118810</RECID>
    <proj_code>118810</proj_code>
    <sub_nr>99900</sub_nr>
    <proj_desc>Nagekomen kosten Oktober 2018</proj_desc>
    <pro_stat>9</pro_stat>
    <comment></comment>
</RECORD>

so i can import it into SQL.

Any thoughts?

As Martin Honnen pointed out, MS SQL Server XQuery doesn't support computed dynamic element names, just literals. Unfortunately, including even the latest SQL Server 2019. Here is an ugly solution.

SQL

DECLARE @xml XML = N'<root>
    <RECORD>
        <RECID>118810</RECID>
        <FIELD TYPE="C">
            <NAME>proj_code</NAME>
            <VALUE>118810</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>sub_nr</NAME>
            <VALUE>99900</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>proj_desc</NAME>
            <VALUE>Nagekomen kosten Oktober 2018</VALUE>
        </FIELD>
        <FIELD TYPE="N">
            <NAME>pro_stat</NAME>
            <VALUE>9</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>comment</NAME>
            <VALUE></VALUE>
        </FIELD>
    </RECORD>
    <RECORD>
        <RECID>118811</RECID>
        <FIELD TYPE="C">
            <NAME>proj_code</NAME>
            <VALUE>118811</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>sub_nr</NAME>
            <VALUE>99901</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>proj_desc</NAME>
            <VALUE>Nagekomen kosten November 2019</VALUE>
        </FIELD>
        <FIELD TYPE="N">
            <NAME>pro_stat</NAME>
            <VALUE>19</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>comment</NAME>
            <VALUE>wow</VALUE>
        </FIELD>
    </RECORD>
</root>';

DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, RECID VARCHAR(10), [col_name] VARCHAR(20), [col_value] VARCHAR(100));

INSERT INTO @tbl
SELECT c.value('(../RECID/text())[1]', 'VARCHAR(100)') AS [RECID] 
    , c.value('(NAME/text())[1]', 'VARCHAR(30)') AS [name]
    , c.value('(VALUE/text())[1]', 'VARCHAR(100)') AS [value]
FROM @xml.nodes('root/RECORD/FIELD') AS t(c);

DECLARE @RowCount INT = (SELECT MAX(ID) FROM @tbl)
    , @recID varchar(10) = (SELECT TOP(1) RECID FROM @tbl WHERE ID = 1)
    , @xml_data VARCHAR(MAX) = '<root><RECORD>';

WHILE @RowCount > 0 BEGIN
    SELECT @xml_data += IIF(@recID != RECID, '</RECORD><RECORD>', '') + 
        --'<' + [col_Name] + '>' + COALESCE([col_value],'') + '</' + [col_Name] + '>'
        CONCAT('<',[col_Name],'>',[col_value],'</',[col_Name],'>')
        , @recID = RECID
    FROM @tbl 
    ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;

    SET @RowCount -= 1;
END;

SET @xml_data += '</RECORD></root>';
SELECT CAST(@xml_data AS XML);

Database and XML Technologies: First International XML Database , First International XML Database Symposium, XSYM 2003, Berlin, Germany, defined in an XQuery query RETURN clause and output them in a flat relation. [​9] or a nest operator for NF2 relations, then convert it into XML documents. apply grouping on the relation using dname as the key, we have the nested relation  Core Computer Group employed Progress DataDirect XQuery to quickly transform flat files and proprietary formats into the XML open standard. Now, a large extraction from 500MB of XML data for building datasets intended for SAS analysis takes significantly less time compared to using different methods.

Simply map each RECORD to a new one where you map all FIELDs to elements with the name from NAME and the value from VALUE:

RECORD !
<RECORD>
{
    RECID,
    FIELD ! element { NAME } { data(VALUE) }
}    
</RECORD>

https://xqueryfiddle.liberty-development.net/nbUY4kB

For XQuery 1 you would need to use for return instead of the map operator !:

for $record in //RECORD
return
<RECORD>
{
    $record/RECID,
    for $field in $record/FIELD 
    return element { $field/NAME } { data($field/VALUE) }
}    
</RECORD>

https://xqueryfiddle.liberty-development.net/nbUY4kB/1

How To : Flatten out an XML Hierarchical Structure, For instance, converting this : ABC XYZ to TAG_ID TAG_NAME each involving a recursive approach with XQuery, XSLT or SQL. I'll be using the following XML document (stored in a binary XMLType each xml has dynamically nested path, some can have say 5 levels, other can have say 10 levels, etc. I have an XML file that I'd like to read into a data frame using xml2, but despite a few hours Google searching, I was unsuccessful. This is probably because I'm not at all familiar with XML. And after all that, I found a 2 liner with XML that mostly does what I'd like (reprex below).

I tried this:

DECLARE @xml xml;

SET @xml =N'

    <RECORD>
        <RECID>118810</RECID>
       <FIELD TYPE="C">
          <NAME>proj_code</NAME>
          <VALUE>118810</VALUE>
       </FIELD>
       <FIELD TYPE="C">
          <NAME>sub_nr</NAME>
          <VALUE>99900</VALUE>
       </FIELD>
       <FIELD TYPE="C">
          <NAME>proj_desc</NAME>
          <VALUE>Nagekomen kosten Oktober 2018</VALUE>
       </FIELD>
       <FIELD TYPE="N">
          <NAME>pro_stat</NAME>
          <VALUE>9</VALUE>
       </FIELD>
       <FIELD TYPE="C">
          <NAME>comment</NAME>
          <VALUE></VALUE>
       </FIELD>
    </RECORD>
    <RECORD>
       <RECID>118811</RECID>
       <FIELD TYPE="C">
          <NAME>proj_code</NAME>
          <VALUE>118811</VALUE>
       </FIELD>
       <FIELD TYPE="C">
          <NAME>sub_nr</NAME>
          <VALUE>99900</VALUE>
       </FIELD>
       <FIELD TYPE="C">
          <NAME>proj_desc</NAME>
          <VALUE>Nagekomen kosten November 2018</VALUE>
       </FIELD>
       <FIELD TYPE="N">
          <NAME>pro_stat</NAME>
          <VALUE>9</VALUE>
       </FIELD>
       <FIELD TYPE="C">
          <NAME>comment</NAME>
          <VALUE></VALUE>
       </FIELD>
    </RECORD>
    ';

SELECT @xml.query('
    for $record in //RECORD
    return
    <RECORD>
    {
    $record/RECID,
    for $field in $record/FIELD 
       return element { $field/NAME } { data($field/VALUE) }
    }    
    </RECORD>
' ) as result

But i get an error on the return element part: XQuery [query()]: Only constant expressions are supported for the name expression of computed element and attribute constructors.

Convert nested XML to flat one - Wrox Programmer Forums, Please help me. I need to convert nested XML to flat one: [u]Source file: &nbs. </Line> </root> When I am using "for each" XSLT broking the order of the elements: Author, XSLT 2.0 and XPath 2.0 Programmer's Reference  The following link has my post having example to convert Xml to HTML using XSLT. You can use the XSL the same way but without using HTML tags and arrange the data in proper format you want it in txt file. Then you just need to write the flat file instead of HTML file as shown in my example

This is the final version with select for inserting it into SQL:

DECLARE @xml XML = N'
<AVXML>
    <SIGNONMSGRS>
        <DTSERVER>2019-09-10T15:54:32</DTSERVER>
        <APPID>ACCOUNTVIEW</APPID>
        <APPVER>0908-</APPVER>
    </SIGNONMSGRS>  
   <EBUSMSGSRS>
      <EBUSQRYRS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RECORD>
        <RECID>118810</RECID>
        <FIELD TYPE="C">
            <NAME>proj_code</NAME>
            <VALUE>118810</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>sub_nr</NAME>
            <VALUE>99900</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>proj_desc</NAME>
            <VALUE>Nagekomen kosten Oktober 2018</VALUE>
        </FIELD>
        <FIELD TYPE="N">
            <NAME>pro_stat</NAME>
            <VALUE>9</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>comment</NAME>
            <VALUE></VALUE>
        </FIELD>
    </RECORD>
    <RECORD>
        <RECID>118811</RECID>
        <FIELD TYPE="C">
            <NAME>proj_code</NAME>
            <VALUE>118811</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>sub_nr</NAME>
            <VALUE>99901</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>proj_desc</NAME>
            <VALUE>Nagekomen kosten November 2019</VALUE>
        </FIELD>
        <FIELD TYPE="N">
            <NAME>pro_stat</NAME>
            <VALUE>19</VALUE>
        </FIELD>
        <FIELD TYPE="C">
            <NAME>comment</NAME>
            <VALUE>wow</VALUE>
        </FIELD>
    </RECORD>
      </EBUSQRYRS>
   </EBUSMSGSRS>
</AVXML>
';

DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, RECID VARCHAR(10), [col_name] VARCHAR(20), [col_value] VARCHAR(100));

INSERT INTO @tbl
SELECT c.value('(../RECID/text())[1]', 'VARCHAR(100)') AS [RECID] 
    , c.value('(NAME/text())[1]', 'VARCHAR(30)') AS [name]
    , c.value('(VALUE/text())[1]', 'VARCHAR(500)') AS [value]
FROM @xml.nodes('//RECORD/FIELD') AS t(c);

DECLARE @RowCount INT = (SELECT MAX(ID) FROM @tbl)
    , @recID varchar(10) = (SELECT TOP(1) RECID FROM @tbl WHERE ID = 1)
    , @xml_data VARCHAR(MAX) = '<root><RECORD>';

WHILE @RowCount > 0 BEGIN

    SELECT @xml_data += 
    IIF(@recID != RECID, 
       '<RECID>' + @recID + '</RECID>' +
       '</RECORD><RECORD>', 
       '') + 

        CONCAT('<',[col_Name],'>',[col_value],'</',[col_Name],'>')
        , @recID = RECID
    FROM @tbl 
    ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;

    SET @RowCount -= 1;
END;

SET @xml_data += '<RECID>' + @recID + '</RECID>' + '</RECORD></root>';

DECLARE @handler int;

exec sys.sp_xml_preparedocument @handler OUTPUT, @xml_data;
--print @xml_data

select *
from OPENXML(@handler,'/root/RECORD',11)
WITH
(   
    id nvarchar(50) 'RECID',
    proj_code nvarchar(50) 'proj_code',
    sub_nr nvarchar(50) 'sub_nr',
    proj_desc nvarchar(1000) 'proj_desc',
    pro_stat nvarchar(50) 'pro_stat',
    comment nvarchar(50) 'comment'
)
exec sys.sp_xml_removedocument @handler

XQuery: A Query Language for XML, This document was produced by the W3C XML Query Working Group, and presented using XML, the ability to intelligently query XML data XQuery is derived from an XML query language called Quilt, which in turn borrowed features The various forms of XQuery expressions can be nested with full  This is a free online XML to JSON converter using which you can convert your XML files to JSON files quickly and easily. The process of conversion takes a few seconds. Once completed, you can store the converted file by downloading it to your system. Transform XML to JSON Converter. There are two ways to convert XML files to JSON files using

Use Nested FOR XML Queries, Learn how to use nested FOR XML queries. BCP · Import Flat File Wizard · Import and Export Wizard · Replication · SQL Server You can assign the FOR XML query result to an xml type variable, or use XQuery to query the result, retrieve the ProductModelID attribute value by using the value() method. Use this tool to convert XML into CSV (Comma Separated Values) or Excel. You can also force double quotes around each field value or it will be determined for you. The output CSV header row is optional.

XMLTABLE : Convert XML Data into Rows and Columns using SQL, XMLTABLE : Convert XML Data into Rows and Columns using SQL XML; Nested XML; XML Data in Variables; Filtering Rows with XPath; Performance. Please note: If you want to convert an Excel XML file (Microsoft Office XML format) into Excel XLSX format - use our Excel XML to Excel XLSX Online Converter. XML Validation As a first step, we read an XML source file and validate it.

fn:parse-xml, XPath and XQuery Functions and Operators 3.1 reference for fn:parse-xml. text to the fn:parse-xml function to create a tree representation of the nested document. This can be done by reading the raw text using the fn:unparsed-text function, editing Select function append · filter · flatten · fold-left · fold-right · for​-each  Thanks for contributing an answer to Code Review Stack Exchange! Please be sure to answer the question. Provide details and share your research! But avoid … Asking for help, clarification, or responding to other answers. Making statements based on opinion; back them up with references or personal experience. Use MathJax to format equations.

Comments
  • Ugly, but it works! i missed the RECID, but i added that one. Thnx for the help.
  • That seems to be a restriction on the XQuery subset your database system (MS SQL server?) imposes. Let's see whether others can help in that area, if there is a tag dedicated to your database system and/or its XQuery support add that to the question.