'How to run sql query that use "for json" and "for xml" in sql server?

I want to run a complicated SQL query that use "for sql" and "for xml" in microsoft sql server. I used ExecuteSQL, but it got me this Error:

ExecuteSQL[id=87f3d800-016c-1000-28be-8d99127d267e] Unable to execute SQL {my sql query} for StandardFlowFileRecord[uuid=ef1bc7c3-2e48-4911-abea-52e9b5a432b2,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1565651606554-2, container=default, section=2], offset=176, length=8],offset=0,name=ef1bc7c3-2e48-4911-abea-52e9b5a432b2,size=8] due to org.apache.avro.SchemaParseException: Illegal character in: XML_F52E2B61-18A1-11d1-B105-00805F49916B; routing to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.avro.SchemaParseException: Illegal character in: XML_F52E2B61-18A1-11d1-B105-00805F49916B

How can I get the result as JSON or XML in apache NiFi? Format Query Results as JSON with FOR JSON (SQL Server)

apache-nifi


Solution 1:[1]

note: i have no experience with such queries and how they are returned to the client.

i assume that server returns one row and one column with string containing json (or xml)

in this case the script for ExecuteGroovyScript could be like this:

def ff=session.get()
if(!ff)return

def query = '''
   SELECT TOP 2 ArtistName, 
   (SELECT AlbumName FROM Albums 
      WHERE Artists.ArtistId = Albums.ArtistId FOR JSON PATH) AS Albums 
   FROM Artists ORDER BY ArtistName FOR JSON PATH
'''

ff.write("UTF-8"){writer-> 
    SQL.mydb.eachRow{row->
        writer.append( row[1] ) //get first column from the row
        writer.append('\n')     //expecting 1 row but just in case add separation for the next row
    }
}
//transfer to success
REL_SUCCESS << ff

to make SQL.mydb available for the script - create property with this name and connect it to corresponding DBCP: https://i.stack.imgur.com/C83f5.png

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 daggett

Related Questions