Vladislav Kudriašev Portfoolio

en_US
Noorem Tarkvaraarendaja eriala

XML Reis

  1. Andmete loomiseks kasutame Mockaroo’d.
  1. Loome andmebaas.
SQL
CREATE DATABASE XMLReis
  1. Kasutame andmebaas
SQL
USE XMLReis
  1. Loome tabel
SQL
create table MOCK_DATA (
	trip_id INT,
	departure_airport VARCHAR(100),
	arrival_airport VARCHAR(100),
	departure_date DATE,
	arrival_date DATE,
	transport_type VARCHAR(5),
	distance_km DECIMAL(7,2),
	duration_hours DECIMAL(4,2),
	passenger_count INT,
	airline_name VARCHAR(50),
	ticket_price DECIMAL(6,2)
);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (1, 'Alexandria Homestead Airport', 'Dos Lagunas Airport', '4/13/2022', '9/22/2022', 'train', 4811.95, 3.98, 1, 'Lazz', 932.92);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (2, 'Telegraph Harbour Seaplane Base', 'Luxembourg-Findel International Airport', '7/28/2022', '3/1/2022', 'plane', 4855.23, 5.94, 8, 'Fatz', 559.84);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (3, 'Santa Rosa Airport', 'Licenciado Gustavo Díaz Ordaz International Airport', '3/8/2022', '5/17/2022', 'train', 3974.36, 12.77, 8, 'Bubblebox', 441.21);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (4, 'Hólmavík Airport', 'Karl Stefan Memorial Airport', '3/31/2022', '1/30/2022', 'car', 4156.16, 2.57, 8, 'Geba', 386.47);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (5, 'Mustique Airport', 'Juanda International Airport', '11/20/2022', '9/20/2022', 'plane', 2584.44, 4.91, 5, 'Quaxo', 247.13);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (6, 'Creston Valley Regional Airport - Art Sutcliffe Field', 'Los Alamos Airport', '7/11/2022', '2/19/2022', 'plane', 6000.88, 18.62, 10, 'Feedspan', 225.28);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (7, 'Mong Hsat Airport', 'Tsumeb Airport', '8/13/2022', '11/25/2022', 'train', 502.29, 18.17, 8, 'Jabbersphere', 496.07);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (8, 'Tuscaloosa Regional Airport', 'Juan Mendoza Airport', '7/8/2022', '7/11/2022', 'car', 4911.61, 13.43, 5, 'Mudo', 617.61);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (9, 'Chicago Rockford International Airport', 'Negage Airport', '12/23/2022', '11/2/2022', 'plane', 8447.41, 1.04, 6, 'Ntags', 173.12);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (10, 'Tekin Airport', 'Kashechewan Airport', '8/6/2022', '9/14/2022', 'car', 3586.65, 13.62, 3, 'Skimia', 568.61);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (11, 'Kunua Airport', 'Diavik Airport', '3/9/2022', '9/30/2022', 'train', 2103.32, 11.9, 4, 'Cogibox', 862.38);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (12, 'Benito Salas Airport', 'Mid Delta Regional Airport', '8/30/2022', '3/9/2022', 'plane', 8780.32, 1.58, 1, 'Brainsphere', 334.21);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (13, 'Mission Field', 'Fazenda Barra do Agudo Airport', '1/5/2022', '7/30/2022', 'car', 2792.03, 23.51, 10, 'Zazio', 669.13);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (14, 'Palanga International Airport', 'Kingaroy Airport', '11/20/2022', '7/11/2022', 'train', 9145.35, 8.08, 9, 'Yadel', 397.22);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (15, 'Three Rivers Municipal Dr Haines Airport', 'Skagit Regional Airport', '2/8/2022', '11/14/2022', 'car', 2709.62, 3.75, 4, 'Quinu', 388.82);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (16, 'Luang Phabang International Airport', 'Sardar-e-Jangal Airport', '4/3/2022', '3/28/2022', 'train', 9360.4, 16.72, 4, 'Babbleblab', 838.25);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (17, 'Cuneo International Airport', 'Qazvin Airport', '1/21/2022', '11/15/2022', 'car', 8154.86, 19.39, 1, 'Flipstorm', 283.49);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (18, 'Hato International Airport', 'Cotswold Airport', '11/12/2022', '12/1/2022', 'plane', 135.19, 10.61, 5, 'Skivee', 785.84);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (19, 'Londolozi Airport', 'Croydon Airport', '6/2/2022', '1/29/2022', 'train', 6185.03, 1.08, 2, 'Realbridge', 697.2);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (20, 'Telêmaco Borba Airport', 'Dabo Airport', '1/21/2022', '4/16/2022', 'plane', 3150.76, 10.58, 2, 'Kwilith', 722.22);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (21, 'Travis Air Force Base', 'Mitchell Municipal Airport', '10/30/2022', '8/24/2022', 'train', 7339.92, 8.88, 2, 'Quinu', 955.13);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (22, 'Ames Municipal Airport', 'Ganzhou Airport', '8/22/2022', '6/7/2022', 'train', 5033.51, 10.68, 10, 'Thoughtsphere', 215.02);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (23, 'Leaf Rapids Airport', 'Kasompe Airport', '6/27/2022', '7/3/2022', 'car', 510.21, 12.15, 8, 'Aivee', 398.0);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (24, 'Serui Airport', 'Bakkafjörður Airport', '5/22/2022', '6/17/2022', 'train', 1605.65, 2.45, 3, 'Babbleset', 303.6);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (25, 'Miraflores Airport', 'Awar Airport', '3/2/2022', '4/4/2022', 'plane', 2064.85, 4.39, 8, 'Flashset', 604.79);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (26, 'Mayaguana Airport', 'Dongsha Island Airport', '7/15/2022', '1/27/2022', 'train', 2709.21, 0.17, 10, 'Dabshots', 280.29);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (27, 'Cambridge Dorchester Airport', 'Eduardo Gomes International Airport', '11/3/2022', '7/8/2022', 'car', 8402.5, 9.32, 8, 'Bubblebox', 530.97);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (28, 'Vung Tau Airport', 'Dimmit County Airport', '11/20/2022', '10/1/2022', 'train', 5183.67, 7.66, 9, 'Browsebug', 300.43);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (29, 'Vanua Balavu Airport', 'Gush Katif Airport', '4/18/2022', '11/26/2022', 'car', 2566.6, 20.6, 1, 'Rhynyx', 435.12);
insert into MOCK_DATA (trip_id, departure_airport, arrival_airport, departure_date, arrival_date, transport_type, distance_km, duration_hours, passenger_count, airline_name, ticket_price) values (30, 'Lusambo Airport', 'Tocumwal Airport', '1/16/2022', '9/15/2022', 'car', 4101.41, 3.54, 4, 'Gigashots', 81.45);
  1. Loome SELECT päring
SQL
SELECT 
    'Reisid' AS Root, 
    (
        SELECT 
            trip_id AS 'reis_id',
            (
                SELECT 
                    departure_airport AS 'Väljumis_lennujaam', 
                    arrival_airport AS 'Sõidukoha_lennujaam'
                FOR XML PATH('Lennujaamad'), TYPE
            ),
            (
                SELECT 
                    transport_type AS 'Transpordi_liik',
                    distance_km AS 'Kaugus_km',
                    duration_hours AS 'Kestus_tunnid',
                    passenger_count AS 'Reisijate_arv',
                    airline_name AS 'Lennufirma_nimi',
                    ticket_price AS 'Piletihind'
                FOR XML PATH('Andmed'), TYPE
            )
        FROM MOCK_DATA
        FOR XML PATH('Reis'), TYPE
    ) AS Reisid
FOR XML PATH('Reis'), ROOT('Reisid')
  1. C# kood

Funktsioon GetXmlFromQuery loob ühenduse SQL Serveri andmebaasiga nimega XMLReis.

Seejärel käivitab see SQL-päringu, mis teisendab MOCK_DATA tabeli andmed XML-vormingusse.

OnGet meetod võtab andmebaasist saadud XML-i ja rakendab sellele Reisid.xslt failis määratletud XSLT teisenduse.

Teisendatud XML salvestatakse TransformedXml atribuuti, mis kuvatakse veebilehel.

C#
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Data.SqlClient;
using System.Text;
using System.Xml;
using System.Xml.Xsl;

namespace XML_Reis.Pages
{
    public class IndexModel : PageModel
    {
        public string TransformedXml { get; private set; }

        private readonly IWebHostEnvironment _hostingEnvironment;

        public IndexModel(IWebHostEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }

        public void OnGet()
        {
            try
            {
                var xml = GetXmlFromQuery();
                var xsltPath = Path.Combine(_hostingEnvironment.ContentRootPath, "wwwroot", "Reisid.xslt");

                var xslt = new XslCompiledTransform();
                xslt.Load(xsltPath);

                using (var stringReader = new StringReader(xml))
                using (var xmlReader = XmlReader.Create(stringReader))
                using (var sw = new StringWriter())
                {
                    xslt.Transform(xmlReader, null, sw);
                    TransformedXml = sw.ToString();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        public static string GetXmlFromQuery()
        {
            string connectionString = "Server=(localdb)\\MSSQLLocalDB;Database=XMLReis;";

            string queryString = @"
            SELECT 
                'Reisid' AS Root, 
                (
                    SELECT 
                        trip_id AS 'reis_id',
                        (
                            SELECT 
                                departure_airport AS 'Väljumis_lennujaam', 
                                arrival_airport AS 'Sõidukoha_lennujaam'
                            FOR XML PATH('Lennujaamad'), TYPE
                        ),
                        (
                            SELECT 
                                transport_type AS 'Transpordi_liik',
                                distance_km AS 'Kaugus_km',
                                duration_hours AS 'Kestus_tunnid',
                                passenger_count AS 'Reisijate_arv',
                                airline_name AS 'Lennufirma_nimi',
                                ticket_price AS 'Piletihind'
                            FOR XML PATH('Andmed'), TYPE
                        )
                    FROM MOCK_DATA
                    FOR XML PATH('Reis'), TYPE
                ) AS Reisid
            FOR XML PATH('Reis'), ROOT('Reisid')";

            StringBuilder xmlOutput = new StringBuilder();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(queryString, connection))
                    {
                        using (XmlReader reader = command.ExecuteXmlReader())
                        {
                            while (reader.Read())
                            {
                                xmlOutput.Append(reader.ReadOuterXml());
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
            }

            string xmlResult = xmlOutput.ToString();

            return xmlResult;
        }
    }
}
  1. XML-skeem:
XML
<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="Reisid">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="Reis">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="Root" type="xs:string" />
							<xs:element name="Reisid">
								<xs:complexType>
									<xs:sequence>
										<xs:element maxOccurs="unbounded" name="Reis">
											<xs:complexType>
												<xs:sequence>
													<xs:element name="reis_id" type="xs:unsignedByte" />
													<xs:element name="Lennujaamad">
														<xs:complexType>
															<xs:sequence>
																<xs:element name="Väljumis_lennujaam" type="xs:string" />
																<xs:element name="Sõidukoha_lennujaam" type="xs:string" />
															</xs:sequence>
														</xs:complexType>
													</xs:element>
													<xs:element name="Andmed">
														<xs:complexType>
															<xs:sequence>
																<xs:element name="Transpordi_liik" type="xs:string" />
																<xs:element name="Kaugus_km" type="xs:decimal" />
																<xs:element name="Kestus_tunnid" type="xs:decimal" />
																<xs:element name="Reisijate_arv" type="xs:unsignedByte" />
																<xs:element name="Lennufirma_nimi" type="xs:string" />
																<xs:element name="Piletihind" type="xs:decimal" />
															</xs:sequence>
														</xs:complexType>
													</xs:element>
												</xs:sequence>
											</xs:complexType>
										</xs:element>
									</xs:sequence>
								</xs:complexType>
							</xs:element>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>
  1. Konverteerime fail XML’ist JSON’iks
PHP
<?php
// Laeme XML faili
$xml = simplexml_load_file("XMLFile.xml");

// Teisendame XML objektist JSON-iks
$json = json_encode($xml, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);

// Salvestame faili
file_put_contents("XMLFile.xml", $json);

echo "Konverteerimine valmis! Vaata faili XMLFile.xml";
  1. Kuvame JSON jQuery‘st
HTML
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>jquery ja JSON</title>
    <script src="//code.jquery.com/jquery-1.11.2.min.js"></script>
    <script>
        $(document).ready(function(){
            $.ajax({
                type: "GET",
                url: "JSONFile.json",
                dataType: "json",
                success: kuvaJSON,
                error: vigaJSON
            });
        });

        function vigaJSON(){
            $("#container").append('<p style="color:red;">Probleemid JSON failiga!</p>');
        }

        function kuvaJSON(json){
            $.each(json.Reis.Reisid.Reis, function(index, reis){
                var reis_id = reis.reis_id;
                var valjumis_lennujaam = reis.Lennujaamad["V\u00e4ljumis_lennujaam"];
                var soidukoha_lennujaam = reis.Lennujaamad["S\u00f5idukoha_lennujaam"];
                var transpordi_liik = reis.Andmed.Transpordi_liik;
                var kaugus_km = reis.Andmed.Kaugus_km;
                var kestus_tunnid = reis.Andmed.Kestus_tunnid;
                var reisijate_arv = reis.Andmed.Reisijate_arv;
                var lennufirma_nimi = reis.Andmed.Lennufirma_nimi;
                var piletihind = reis.Andmed.Piletihind;

                $("#container").append(
                    '<div style="border: 1px solid;">' +
                    '<h3>Reis ID: ' + reis_id + '</h3>' +
                    '<p><strong>Väljumis lennujaam:</strong> ' + valjumis_lennujaam + '</p>' +
                    '<p><strong>Sõidukoha lennujaam:</strong> ' + soidukoha_lennujaam + '</p>' +
                    '<p><strong>Transpordi liik:</strong> ' + transpordi_liik + '</p>' +
                    '<p><strong>Kaugus (km):</strong> ' + kaugus_km + '</p>' +
                    '<p><strong>Kestus (tunnid):</strong> ' + kestus_tunnid + '</p>' +
                    '<p><strong>Reisijate arv:</strong> ' + reisijate_arv + '</p>' +
                    '<p><strong>Lennufirma nimi:</strong> ' + lennufirma_nimi + '</p>' +
                    '<p><strong>Piletihind:</strong> ' + piletihind + '</p>' +
                    '</div>'
                );
            });
        }
    </script>
</head>
<body>
<div id="container">

</div>
</body>
</html>

https://github.com/FantomTchi7/Andmevahetusvormingud/tree/master