Exam DP-203 All QuestionsBrowse all questions from this exam
Go to Exam
Question 123

DRAG DROP -

You have an Azure Synapse Analytics workspace named WS1.

You have an Azure Data Lake Storage Gen2 container that contains JSON-formatted files in the following format.

You need to use the serverless SQL pool in WS1 to read the files.

How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

Select and Place:

    Correct Answer:

    Box 1: openrowset -

    The easiest way to see to the content of your CSV file is to provide file URL to OPENROWSET function, specify csv FORMAT.

    Example:

    SELECT *

    FROM OPENROWSET(

    BULK 'csv/population/population.csv',

    DATA_SOURCE = 'SqlOnDemandDemo',

    FORMAT = 'CSV', PARSER_VERSION = '2.0',

    FIELDTERMINATOR =',',

    ROWTERMINATOR = '\n'

    Box 2: openjson -

    You can access your JSON files from the Azure File Storage share by using the mapped drive, as shown in the following example:

    SELECT book.* FROM -

    OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json

    CROSS APPLY OPENJSON(BulkColumn)

    WITH( id nvarchar(100), name nvarchar(100), price float,

    pages_i int, author nvarchar(100)) AS book

    Reference:

    https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-single-csv-file https://docs.microsoft.com/en-us/sql/relational-databases/json/import-json-documents-into-sql-server

Discussion
Maunik

Answer is correct https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files

Lrng15

answer is correct as per this link

gf2tw

The question and answer seem out of place, there was no mention of the CSV and the query in the answer doesn't match up with openjson at all

gssd4scoder

agree with you, very misleading

anto69

agree with u

dev2dev

Look at the WITH statement, the csv column can contain json data.

vctrhugo

The easiest way to see to the content of your JSON file is to provide the file URL to the OPENROWSET function, specify csv FORMAT, and set values 0x0b for fieldterminator and fieldquote.

ELJORDAN23

Answer is correct: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files#query-json-files-using-openjson

evangelist

To complete the Transact-SQL statement for reading JSON-formatted files using the serverless SQL pool in WS1, you should use OPENROWSET to access the data and OPENJSON to parse the JSON content. Here is the correct completion of the statement:

KarlGardnerDataEngineering

This took me about 10 hours to understand this query

kkk5566

Answer is correct

mamahani

openrowset / openjson

zorko10

does openjson do the same thing as jsoncontent ? I tried running a query on a json file and the auto filled code used jsoncontent instead of openjson

Deeksha1234

correct

SebK

Correct

PallaviPatel

correct