Dane JSON i XML z go-mssqldb

SQL Server posiada wbudowane wsparcie dla danych JSON i XML. Ten artykuł pokazuje, jak używać sterownika go-mssqldb do zapytań, wstawiania i transformacji danych JSON i XML między strukturami Go a SQL Server.

Przykłady w tym artykule porównane są z przykładową bazą danych AdventureWorks2025 . Przykłady ukierunkowane na odczyt wysyłają zapytania do wbudowanych obiektów, takich jak Sales.vSalesPerson i Sales.SalesOrderHeader. Przykłady zorientowane na zapis używają OPENJSON i XML .nodes() do wstawiania wierszy do HumanResources.Department.

Większość fragmentów zakłada, że ctx i db są już zainicjalizowane. Możesz użyć tego wzorca przygotowań:

import (
    "context"
    "database/sql"
    "log"
    "time"

    _ "github.com/microsoft/go-mssqldb"
)

db, err := sql.Open("sqlserver", "sqlserver://<user>:<password>@<server>?database=AdventureWorks2025&encrypt=true&TrustServerCertificate=true")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()

Dane JSON

Wyniki zapytań w formacie JSON przy użyciu FOR JSON

Używanie FOR JSON PATH do zwracania wyników zapytań jako ciąg JSON:

var jsonResult string
err := db.QueryRowContext(ctx,
    "SELECT BusinessEntityID AS Id, FirstName + ' ' + LastName AS Name, CountryRegionName AS Department FROM Sales.vSalesPerson WHERE CountryRegionName = @dept FOR JSON PATH",
    sql.Named("dept", "United States")).Scan(&jsonResult)
if err != nil {
    log.Fatal(err)
}
fmt.Println(jsonResult)

Uwaga / Notatka

SQL Server może zwracać dane wyjściowe FOR JSON w wielu wierszach w przypadku większych porcji danych. Jeśli potrzebujesz w sposób niezawodny obsługiwać większe zestawy wyników, odczytaj wszystkie zwrócone wiersze i scal je. Zobacz Obsługę dużych wyników JSON.

Obsługa dużych wyników JSON

Gdy SQL Server zwraca wynik JSON w wielu wierszach, łącz je przed rozłączeniem lub wydrukowaniem wyniku:

import "strings"

func queryJSON(ctx context.Context, db *sql.DB, query string, args ...any) (string, error) {
    rows, err := db.QueryContext(ctx, query, args...)
    if err != nil {
        return "", err
    }
    defer rows.Close()

    var sb strings.Builder
    for rows.Next() {
        var chunk string
        if err := rows.Scan(&chunk); err != nil {
            return "", err
        }
        sb.WriteString(chunk)
    }
    if err := rows.Err(); err != nil {
        return "", err
    }
    return sb.String(), nil
}

Użycie:

jsonStr, err := queryJSON(ctx, db,
    "SELECT BusinessEntityID AS Id, FirstName + ' ' + LastName AS Name, CountryRegionName AS Department FROM Sales.vSalesPerson FOR JSON PATH")
if err != nil {
    log.Fatal(err)
}

JSON Unmarshal prowadzi do struktur Go

Połącz FOR JSON z encoding/json, aby deserializować wyniki SQL Server bezpośrednio do typów w Go:

import "encoding/json"

type Employee struct {
    ID         int    `json:"Id"`
    Name       string `json:"Name"`
    Department string `json:"Department"`
}

func getEmployees(ctx context.Context, db *sql.DB, dept string) ([]Employee, error) {
    var jsonStr string
    err := db.QueryRowContext(ctx,
        "SELECT BusinessEntityID AS Id, FirstName + ' ' + LastName AS Name, CountryRegionName AS Department FROM Sales.vSalesPerson WHERE CountryRegionName = @dept FOR JSON PATH",
        sql.Named("dept", dept)).Scan(&jsonStr)
    if err != nil {
        return nil, err
    }

    var employees []Employee
    if err := json.Unmarshal([]byte(jsonStr), &employees); err != nil {
        return nil, err
    }
    return employees, nil
}

Zagnieżdżony kod JSON przy użyciu FOR JSON PATH

Tworz zagnieżdżone struktury JSON, używając notacji kropek w aliasach kolumn:

var jsonResult string
err := db.QueryRowContext(ctx, `
    SELECT
        soh.SalesOrderID AS [Id],
        soh.OrderDate AS [OrderDate],
        CONCAT(pp.FirstName, ' ', pp.LastName) AS [Customer.Name],
        ea.EmailAddress AS [Customer.Email],
        soh.TotalDue AS [Total]
    FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID
    LEFT JOIN Person.Person AS pp ON c.PersonID = pp.BusinessEntityID
    LEFT JOIN Person.EmailAddress AS ea ON pp.BusinessEntityID = ea.BusinessEntityID
    WHERE soh.SalesOrderID = @id
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER`,
    sql.Named("id", 43659)).Scan(&jsonResult)

Wynik:

{"Id":43659,"OrderDate":"2022-05-30T00:00:00","Customer":{"Name":"James Hendergart","Email":"james9@adventure-works.com"},"Total":23153.2339}

Parse JSON in SQL Server with OPENJSON

Zastosowanie OPENJSON do rozdrabniania tablicy JSON na wiersze po stronie serwera:

jsonData := `[
    {"Name": "Alice", "Department": "Engineering"},
    {"Name": "Bob", "Department": "Marketing"}
]`

rows, err := db.QueryContext(ctx, `
    SELECT Name, Department
    FROM OPENJSON(@json)
    WITH (
        Name NVARCHAR(100) '$.Name',
        Department NVARCHAR(100) '$.Department'
    )`, sql.Named("json", jsonData))
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var name, dept string
    if err := rows.Scan(&name, &dept); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%s - %s\n", name, dept)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

Wstaw dane JSON jako parametr

Wyślij strukturę Go jako ciąg JSON do procedury przechowywanej lub OPENJSON:

type OrderItem struct {
    ProductID int     `json:"productId"`
    Quantity  int     `json:"quantity"`
    Price     float64 `json:"price"`
}

func sendJSONPayload(ctx context.Context, db *sql.DB, metadata map[string]any) error {
    jsonBytes, err := json.Marshal(metadata)
    if err != nil {
        return err
    }

    // Pass JSON to a stored procedure for server-side processing.
    _, err = db.ExecContext(ctx,
        "EXECUTE dbo.ProcessDepartmentMetadata @payload",
        sql.Named("payload", string(jsonBytes)))
    return err
}

Użycie:

metadata := map[string]any{
    "source":  "docs-sample",
    "batchId": "b-20260709-01",
    "departments": []map[string]any{
        {"name": "Data Science", "groupName": "Research and Development"},
        {"name": "Cloud Ops", "groupName": "Information Technology"},
    },
}

if err := sendJSONPayload(ctx, db, metadata); err != nil {
    log.Fatal(err)
}

Wstawianie wsadowe z JSON przy użyciu OPENJSON

Wstawianie wielu wierszy z tablicy JSON w jednej instrukcji:

func bulkInsertFromJSON(ctx context.Context, db *sql.DB, jsonData string) (int64, error) {
    result, err := db.ExecContext(ctx, `
        INSERT INTO HumanResources.Department (Name, GroupName)
        SELECT Name, GroupName
        FROM OPENJSON(@json)
        WITH (
            Name NVARCHAR(50) '$.name',
            GroupName NVARCHAR(50) '$.groupName'
        )`, sql.Named("json", jsonData))
    if err != nil {
        return 0, err
    }
    return result.RowsAffected()
}

Użycie:

jsonData := `[
    {"name": "Data Science", "groupName": "Research and Development"},
    {"name": "Cloud Ops", "groupName": "Information Technology"},
    {"name": "Developer Relations", "groupName": "Sales and Marketing"}
]`

rowsInserted, err := bulkInsertFromJSON(ctx, db, jsonData)
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Inserted %d rows\n", rowsInserted)

JSON_VALUE i JSON_QUERY

Wyodrębniaj wartości z kolumn JSON bez przenoszenia całego dokumentu JSON:

// Extract a scalar value.
var productName string
err := db.QueryRowContext(ctx, `
    SELECT Name
    FROM Production.Product
    WHERE ProductID = @id`,
    sql.Named("id", 1)).Scan(&productName)
if err != nil {
    log.Fatal(err)
}
fmt.Println(productName)

// Extract a JSON array from a FOR JSON subquery.
var itemsJSON string
err = db.QueryRowContext(ctx, `
    SELECT (
        SELECT ProductID, Name
        FROM Production.Product
        WHERE ProductSubcategoryID = @subId
        FOR JSON PATH
    ) AS Items`,
    sql.Named("subId", 1)).Scan(&itemsJSON)
if err != nil {
    log.Fatal(err)
}
fmt.Println(itemsJSON)

Wskazówka

JSON_VALUE zwraca wartość skalarną (łańcuch, liczba). JSON_QUERY zwraca obiekt lub tablicę. Użyj właściwej funkcji dla potrzebnego typu danych.

JSON obliczał kolumny i indeksy

Dla często zapytywanych właściwości JSON utwórz kolumny obliczeniowe z indeksami dla lepszej wydajności:

-- Demo table with JSON-only rows.
IF OBJECT_ID('dbo.ProductJsonDemo', 'U') IS NOT NULL
    DROP TABLE dbo.ProductJsonDemo;

CREATE TABLE dbo.ProductJsonDemo (
    ProductDescriptionID INT IDENTITY(1,1) PRIMARY KEY,
    Metadata NVARCHAR(MAX) NOT NULL,
    CONSTRAINT CK_ProductJsonDemo_Metadata_IsJson CHECK (ISJSON(Metadata) = 1),
    -- Cast to a bounded length so the index key stays under SQL Server limits.
    LocaleName AS CAST(JSON_VALUE(Metadata, '$.locale') AS NVARCHAR(10)) PERSISTED
);

INSERT INTO dbo.ProductJsonDemo (Metadata)
VALUES
    (N'{"locale":"en","title":"Road helmet"}'),
    (N'{"locale":"fr","title":"Casque de route"}');

CREATE INDEX IX_ProductJsonDemo_LocaleName ON dbo.ProductJsonDemo(LocaleName);

Następnie odpytaj bezpośrednio kolumnę obliczaną w Go:

rows, err := db.QueryContext(ctx,
    "SELECT ProductDescriptionID, LocaleName FROM dbo.ProductJsonDemo WHERE LocaleName = @name",
    sql.Named("name", "en"))
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var id int
    var localeName string
    if err := rows.Scan(&id, &localeName); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%d %s\n", id, localeName)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

dane XML

Wyniki zapytania w formacie XML za pomocą FOR XML

Zastosowanie FOR XML PATH do zwracania wyników zapytań w formie XML:

var xmlResult string
err := db.QueryRowContext(ctx, `
    SELECT BusinessEntityID AS [@id], FirstName + ' ' + LastName AS Name, CountryRegionName AS Department
    FROM Sales.vSalesPerson
    WHERE CountryRegionName = @dept
    FOR XML PATH('Employee'), ROOT('Employees')`,
    sql.Named("dept", "United States")).Scan(&xmlResult)
if err != nil {
    log.Fatal(err)
}
fmt.Println(xmlResult)

Wynik:

<Employees>
    <Employee id="274"><Name>Stephen Jiang</Name><Department>United States</Department></Employee>
    <Employee id="275"><Name>Michael Blythe</Name><Department>United States</Department></Employee>
    <Employee id="276"><Name>Linda Mitchell</Name><Department>United States</Department></Employee>
    <Employee id="277"><Name>Jillian Carson</Name><Department>United States</Department></Employee>
    <Employee id="279"><Name>Tsvi Reiter</Name><Department>United States</Department></Employee>
    <Employee id="280"><Name>Pamela Ansman-Wolfe</Name><Department>United States</Department></Employee>
    <Employee id="281"><Name>Shu Ito</Name><Department>United States</Department></Employee>
    <Employee id="283"><Name>David Campbell</Name><Department>United States</Department></Employee>
    <Employee id="284"><Name>Tete Mensa-Annan</Name><Department>United States</Department></Employee>
    <Employee id="285"><Name>Syed Abbas</Name><Department>United States</Department></Employee>
    <Employee id="287"><Name>Amy Alberts</Name><Department>United States</Department></Employee>
</Employees>

Obsługa dużych wyników XML

Podobnie jak w JSON, duże wyniki XML są podzielone na wiersze.

func queryXML(ctx context.Context, db *sql.DB, query string, args ...any) (string, error) {
    rows, err := db.QueryContext(ctx, query, args...)
    if err != nil {
        return "", err
    }
    defer rows.Close()

    var sb strings.Builder
    for rows.Next() {
        var chunk string
        if err := rows.Scan(&chunk); err != nil {
            return "", err
        }
        sb.WriteString(chunk)
    }
    if err := rows.Err(); err != nil {
        return "", err
    }
    return sb.String(), nil
}

Parsowanie wyników XML w Go

Użyj pakietu encoding/xml, aby deserializować wyniki XML.

import "encoding/xml"

type EmployeeList struct {
    XMLName   xml.Name   `xml:"Employees"`
    Employees []Employee `xml:"Employee"`
}

type Employee struct {
    ID         int    `xml:"id,attr"`
    Name       string `xml:"Name"`
    Department string `xml:"Department"`
}

func getEmployeesXML(ctx context.Context, db *sql.DB, dept string) (*EmployeeList, error) {
    xmlStr, err := queryXML(ctx, db, `
        SELECT BusinessEntityID AS [@id], FirstName + ' ' + LastName AS Name, CountryRegionName AS Department
        FROM Sales.vSalesPerson
        WHERE CountryRegionName = @dept
        FOR XML PATH('Employee'), ROOT('Employees')`,
        sql.Named("dept", dept))
    if err != nil {
        return nil, err
    }

    var result EmployeeList
    if err := xml.Unmarshal([]byte(xmlStr), &result); err != nil {
        return nil, err
    }
    return &result, nil
}

Przekazanie parametrów XML do SQL Server

Wyślij dokument XML do procedury przechowywanej lub zapytania:

xmlData := `<Employees>
    <Employee><Name>Alice</Name><Department>Engineering</Department></Employee>
    <Employee><Name>Bob</Name><Department>Marketing</Department></Employee>
</Employees>`

rows, err := db.QueryContext(ctx, `
    DECLARE @xmlDoc XML = CAST(@xml AS XML);
    SELECT
        e.value('(Name)[1]', 'NVARCHAR(100)') AS Name,
        e.value('(Department)[1]', 'NVARCHAR(100)') AS Department
    FROM @xmlDoc.nodes('/Employees/Employee') AS t(e)`,
    sql.Named("xml", xmlData))
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var name, dept string
    if err := rows.Scan(&name, &dept); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%s - %s\n", name, dept)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

Uwaga / Notatka

Parametr @xml jest domyślnie wysyłany jako nvarchar(max). Metoda .nodes() wymaga typu danych xml, więc jawnie rzutuj parametr za pomocą CAST(@xml AS XML).

Wstaw wiersze z XML

Użyj metody .nodes() w instrukcji INSERT...SELECT, aby podzielić kod XML na wiersze tabeli.

func bulkInsertFromXML(ctx context.Context, db *sql.DB, xmlData string) (int64, error) {
    result, err := db.ExecContext(ctx, `
        DECLARE @xmlDoc XML = CAST(@xml AS XML);
        INSERT INTO HumanResources.Department (Name, GroupName)
        SELECT
            e.value('(Name)[1]', 'NVARCHAR(50)'),
            e.value('(GroupName)[1]', 'NVARCHAR(50)')
        FROM @xmlDoc.nodes('/Departments/Department') AS t(e)`,
        sql.Named("xml", xmlData))
    if err != nil {
        return 0, err
    }
    return result.RowsAffected()
}

Wybierz między JSON a XML

Rozważenie	JSON	XML
Wsparcie ekosystemu Go	Standard encoding/json. Tagi strukturalne do mapowania.	Standard encoding/xml. Bardziej rozbudowane tagi strukturalne.
Wsparcie SQL Server	OPENJSON, JSON_VALUE, JSON_QUERY, FOR JSON (SQL Server 2016 i późniejsze wersje)	nodes() value(), , query(), ( FOR XML wszystkie wersje)
Performance	Zazwyczaj szybsze parsowanie. Bardziej zwięzły format transmisji.	Wspiera schematy i walidację. Bardziej szczegółowy.
Weryfikacja schematu	Brak wbudowanej walidacji schematów w SQL Server.	Obsługuje kolekcje XML Schema Collections do walidacji po stronie serwera.
Indeksowanie	Kolumny obliczane z indeksem JSON_VALUE +.	Indeksy XML (pierwotne i wtórne).
Modelowanie danych	Tablice i zagnieżdżone obiekty. Naturalne dopasowanie do slice’ów i map w Go.	Dokumenty hierarchiczne z atrybutami i przestrzeniami nazw.

Wskazówka

Do nowych projektów JSON jest zazwyczaj lepszym wyborem. Wymaga mniejszego narzutu związanego z parsowaniem, generuje mniej danych i w naturalny sposób odwzorowuje struktury Go. Używaj XML, gdy potrzebujesz walidacji schematu lub integrujesz się z systemami wymagającymi XML.

Treści powiązane

• Zapytania i oświadczenia
• Mapowania typów danych
• Procedury składowane
• Dostrajanie wydajności