QueryDocumentBuilder.cs

using System.Globalization;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Xml;

namespace XmlDataSourceProxy;

/// <summary>
/// Builds an SSRS XML data source &lt;Query&gt; document with an
/// &lt;ElementPath&gt; that lists every flattened field discovered in the
/// upstream JSON sample, annotated with an inferred SSRS field type.
///
/// The generated document is intended to be fetched at design time by
/// appending ?_xquery=1 to a proxied URL, and pasted directly into the
/// "Query" box of an SSRS XML dataset.
///
/// Inference scans up to MaxSampleRows rows and votes per field:
///   * If every observed non-null value parses as Integer  -&gt; Integer
///   * Else if every value parses as Long                  -&gt; Long
///   * Else if every value parses as Decimal               -&gt; Decimal
///   * Else if every value parses as Boolean               -&gt; Boolean
///   * Else if every value parses as ISO-8601 DateTime     -&gt; DateTime
///   * Else                                                 -&gt; String (default,
///     emitted without the type annotation to keep the query compact)
/// </summary>
public static class QueryDocumentBuilder
{
    private const int MaxSampleRows = 50;

    private static readonly Regex Iso8601 = new(
        @"^\d{4}-\d{2}-\d{2}([Tt ]\d{2}:\d{2}(:\d{2}(\.\d+)?)?(Z|[+-]\d{2}:?\d{2})?)?$",
        RegexOptions.Compiled);

    private enum FieldType { Integer, Long, Decimal, Boolean, DateTime, String }

    public static byte[] Build(JsonElement root, string? rowPath)
    {
        // Preserve discovery order across rows.
        var fieldOrder = new List<string>();
        var fields = new Dictionary<string, FieldState>(StringComparer.Ordinal);

        var rowCount = 0;
        foreach (var row in JsonToXmlResponseTransform.SelectRowsPublic(root, rowPath))
        {
            if (rowCount++ >= MaxSampleRows) break;

            switch (row.ValueKind)
            {
                case JsonValueKind.Object:
                    foreach (var (name, value) in JsonToXmlResponseTransform.FlattenObjectPublic(row))
                    {
                        RecordSample(fieldOrder, fields, name, value);
                    }
                    break;
                default:
                    RecordSample(fieldOrder, fields, "Value", row);
                    break;
            }
        }

        return Render(fieldOrder, fields, rowPath);
    }

    private static void RecordSample(
        List<string> order,
        Dictionary<string, FieldState> map,
        string name,
        JsonElement value)
    {
        if (!map.TryGetValue(name, out var state))
        {
            state = new FieldState();
            map[name] = state;
            order.Add(name);
        }

        state.Observe(value);
    }

    private static byte[] Render(
        List<string> order,
        Dictionary<string, FieldState> fields,
        string? rowPath)
    {
        var sb = new StringBuilder();
        sb.AppendLine("<!--");
        sb.AppendLine("  Generated by XmlDataSourceProxy for SSRS / Power BI Report Server.");
        sb.AppendLine("  Paste the <Query>...</Query> block below into the dataset Query box.");
        if (!string.IsNullOrWhiteSpace(rowPath))
        {
            sb.AppendLine($"  Row path applied at proxy: {rowPath}");
        }
        sb.AppendLine($"  Fields discovered: {order.Count}");
        sb.AppendLine("-->");
        sb.AppendLine("<Query>");
        sb.AppendLine("  <ElementPath IgnoreNamespaces=\"true\">Root/Row{");

        for (var i = 0; i < order.Count; i++)
        {
            var name = order[i];
            var safe = JsonToXmlResponseTransform.SanitizeNamePublic(name);
            var t = fields[name].Resolve();
            var typed = t == FieldType.String ? safe : $"{safe}({t})";
            var comma = i < order.Count - 1 ? "," : string.Empty;
            sb.AppendLine($"    {typed}{comma}");
        }

        sb.AppendLine("  }</ElementPath>");
        sb.AppendLine("</Query>");

        // Emit with XML declaration so SSRS / browsers render cleanly.
        var encoding = new UTF8Encoding(false);
        var body = encoding.GetBytes("<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n" + sb);
        return body;
    }

    private sealed class FieldState
    {
        public bool SeenAny;
        public bool AllInteger = true;
        public bool AllLong = true;
        public bool AllDecimal = true;
        public bool AllBoolean = true;
        public bool AllDateTime = true;

        public void Observe(JsonElement value)
        {
            switch (value.ValueKind)
            {
                case JsonValueKind.Null:
                case JsonValueKind.Undefined:
                    return; // nulls don't constrain the type
                case JsonValueKind.Array:
                case JsonValueKind.Object:
                    // Emitted as JSON text -> definitely a string.
                    Demote(FieldType.String);
                    return;
            }

            SeenAny = true;

            switch (value.ValueKind)
            {
                case JsonValueKind.True:
                case JsonValueKind.False:
                    // A boolean cannot also be a number/date.
                    AllInteger = AllLong = AllDecimal = AllDateTime = false;
                    break;

                case JsonValueKind.Number:
                    AllBoolean = false;
                    AllDateTime = false;
                    if (AllInteger && !value.TryGetInt32(out _)) AllInteger = false;
                    if (AllLong    && !value.TryGetInt64(out _)) AllLong = false;
                    if (AllDecimal && !value.TryGetDecimal(out _)) AllDecimal = false;
                    break;

                case JsonValueKind.String:
                    var s = value.GetString() ?? string.Empty;
                    if (AllInteger && !int.TryParse(s, NumberStyles.Integer, CultureInfo.InvariantCulture, out _)) AllInteger = false;
                    if (AllLong    && !long.TryParse(s, NumberStyles.Integer, CultureInfo.InvariantCulture, out _)) AllLong = false;
                    if (AllDecimal && !decimal.TryParse(s, NumberStyles.Number, CultureInfo.InvariantCulture, out _)) AllDecimal = false;
                    if (AllBoolean && !(s.Equals("true", StringComparison.OrdinalIgnoreCase)
                                     || s.Equals("false", StringComparison.OrdinalIgnoreCase))) AllBoolean = false;
                    if (AllDateTime && !LooksLikeIsoDate(s)) AllDateTime = false;
                    break;
            }
        }

        private void Demote(FieldType to)
        {
            AllInteger = AllLong = AllDecimal = AllBoolean = AllDateTime = false;
        }

        public FieldType Resolve()
        {
            if (!SeenAny) return FieldType.String;
            if (AllBoolean) return FieldType.Boolean;
            if (AllInteger) return FieldType.Integer;
            if (AllLong)    return FieldType.Long;
            if (AllDecimal) return FieldType.Decimal;
            if (AllDateTime) return FieldType.DateTime;
            return FieldType.String;
        }
    }

    private static bool LooksLikeIsoDate(string s)
    {
        if (string.IsNullOrEmpty(s)) return false;
        if (!Iso8601.IsMatch(s)) return false;
        // Final sanity check via DateTime.TryParse with invariant culture.
        return DateTime.TryParse(s, CultureInfo.InvariantCulture,
            DateTimeStyles.RoundtripKind | DateTimeStyles.AssumeUniversal, out _);
    }
}