Gist ba39cf5b6349b2131c577e2e046e8654

100%

Gist ba39cf5b6349b2131c577e2e046e8654

✓ Published0🌍 Public

Last edited Apr 11, 2017

Created on Apr 10, 2017

This example demonstrates a data-driven approach to XML serialization within the Power Query language, showing how database queries can be wrapped in a custom function. The code defines a let expression that calls the `Value.NativeQuery` function, targeting a SQL Server database named "QueryPower" and executing a stored procedure called `usp_InsertDemoXML` with a binary parameter. The procedure receives an XML payload stored in a variable named `dummy2XML`, which is passed as the `xmlBin` argument. This illustrates a pattern for embedding XML data into SQL Server stored procedures through Power Query, highlighting how native database queries can be integrated into data transformation workflows. The example focuses on the parameter-passing mechanism rather than visual rendering.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist 8ddacfa41ce888f458863185ecfc400f

This example demonstrates a stored procedure that converts XML data into relational rows for insertion into a database table. The code defines `usp_InsertDemoXML`, which accepts a `varbinary(max)` parameter, casts it to XML, and then uses XQuery’s `nodes()` and `value()` methods to extract `key` and `value` pairs from each `<row>` element. It shows a data-ingestion pattern for parsing structured XML payloads into a normalized key-value table structure. The procedure relies on T-SQL’s built-in XML support rather than external visualization libraries, focusing on server-side data transformation.

HHugoberry

79% match

Loading thumbnail…

Gist fad134c9dcebee3dc1c56a9cb22de435

This example demonstrates a Power Query M function that serializes a table into a JSON object and then passes that JSON to a SQL Server stored procedure, `usp_InsertDemoJSON`, which unwraps it as a recordset and performs an INSERT operation. The code uses `Text.FromBinary` and `Json.FromValue` to convert the table, then `Value.NativeQuery` to execute the stored procedure with the JSON payload. It shows a data pipeline from a Power BI or Excel data source into a SQL database, relying on the M language’s native JSON handling and SQL Server’s JSON support.

HHugoberry

76% match

Loading thumbnail…

Dummy table serialized as XML binary

This example shows a Power Query routine that serializes a two-column table into a single binary XML document. The code transforms each row into a `<row>` element containing `<key>` and `<value>` tags, then wraps all rows in a root `<table>` element. It relies on the `Table.TransformRows` function to iterate over the input table, while `Binary.Combine` and `Text.ToBinary` concatenate the textual XML fragments into a single binary output. The data source is an in-memory table named `dummy`, and the result is a well-formed XML representation of that table.

HHugoberry

73% match

Loading thumbnail…

Gist 47d998760fa7271ac3157ce7d9555612

This example demonstrates how to query and load data from external sources using Power Query's OleDb.Query function, specifically connecting to a local CSV file and an XML catalog. The code shows two approaches: one using Microsoft's ACE OLEDB provider to read a delimited text file, and another using the MSDAOSP provider with MSXML2.DSOControl to access XML data. The visualization highlights the raw query definitions rather than rendering graphical output, focusing on the data-acquisition layer. Both queries reference file paths on the local system, with the CSV query specifying the file directory and the XML query pointing to a catalog file.

HHugoberry

71% match

Loading thumbnail…

PowerQuery an alternative to opening XML files

This example demonstrates how PowerQuery can serve as an alternative to manually opening and parsing XML files. It shows a direct query that uses the `OleDb.Query` function with the `MSDAOSP` provider and the `MSXML2.DSOControl` data source to access an XML catalog. The code specifies a file path, `c:\code\catalog.xml`, and executes the query to load its contents. This approach highlights a practical way to integrate XML data into data-analysis workflows without a separate parser. The visualization makes the data-access method visible, emphasizing the tool's role in bridging XML files and PowerQuery’s querying capabilities.

HHugoberry

70% match

Loading thumbnail…

Gist faec7eb6c03589ff608bab991699407b

This example demonstrates how to generate and execute a dynamic SQL script in Power Query (M language) to bulk-insert data into a SQL Server table using a table-valued parameter (TVP). It builds a textual TVP declaration and populates it by chunking source rows into sets of 1,000 entries, then constructs a multi-statement script that inserts each chunk and calls a stored procedure. The code relies on M functions like `Table.TransformRows`, `List.Generate`, `Text.Combine`, and `Value.NativeQuery` to programmatically assemble and run the query. The visualization highlights the data flow from a source table to a SQL stored procedure, emphasizing the chunking logic and the use of native query execution.

HHugoberry

69% match

Loading thumbnail…

Exporting data out of Power Query via AdoDb route

This example demonstrates how to export data out of Power Query by directly inserting rows into a SQL Server table through an ADO.NET connection. It uses the `Sql.Database` function to connect to a local server instance and the `Value.NativeQuery` API to execute a parameterized `INSERT` statement, passing literal values for the `@key` and `@value` parameters. The code shows a concrete way to push query results into an external database, bypassing Excel’s native output options. The visible data source is a SQL table named `[dbo].[Demo]`, and the approach highlights ADO.NET’s role in bridging Power Query transformations to persistent storage.

HHugoberry

68% match

Loading thumbnail…

Power Query export data to SQL Server

This example demonstrates exporting data from a SQL Server database by running a large SQL script stored in a local file. It shows how Power Query can leverage native database connectivity to execute a bulk insert operation with high parallelism. The code uses `File.Contents` to read the SQL file, then passes its binary content to `Value.NativeQuery`, which targets a SQL Server instance via `Sql.Database`. Key parameters include `EnableBulkInsert` for efficient data loading, `MaxDegreeOfParallelism` to speed up execution, and explicit timeouts for command and connection. The rendered output is the result set from the query, effectively transferring data into Power BI’s data model.

HHugoberry

68% match