, Guest>> || || |
/ Delphi
 PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
, !

, , .

XML PG

:

procedure TfImportEGRUL.Button2Click(Sender: TObject);
var
i: integer;
FName: string;
begin
     if OpenDialog1.Execute Then
     Begin
      FName:=OpenDialog1.FileName;
      XMLDocument.LoadFromFile(FName);
      for i:=0 to XMLDocument.ChildNodes.Count - 1 do
      dmUnit.DM.MainTableQuery.SQL.Clear;
      dmUnit.DM.MainTableQuery.SQL.Text :='INSERT INTO VU_VK_ORG.maintable (fullname) VALUES ()';
      dmUnit.DM.MainTableQuery.Params.ParamByName('maintable.fullname').Value:=XMLdocument.DocumentElement.ChildNodes[''];
      dmUnit.DM.MainTableQuery.Open;
      dmUnit.DM.MainTableQuery.Active := True;
     End;
end;


:

CREATE TABLE VU_VK_ORG.MainTable(
		id				serial PRIMARY KEY not null,
		fullname		        character(300), --   
		abbrname		        character(200), --   
		IndexAdres 		character(6),,
		AdresNameReg 	        character(50),
		AdresTipReg 	        character(30),
		AdresTipSity 	        character(20),
		AdresNameSity 	        character(20),
		AdresTipStreet 	        character(20),
		AdresNameStreet       character(50),
		AdresHome 		character(10),
		AdresKorp 		character(5),
		AdresOffice 	        character(5),
		Email 			character(30),
		Famhead			character(20),
		Namehead 		character(20),
		FNamehead 		character(20),
		headpos			character(120), --   
		TLFhead			character(20), --   
		FamDL 			character(20),
		NameDL 			character(20),		
		FnameDL			character(20),
		TLFrespon		        character(20),
		superorg		        character(200),
		INN				character(10),
		OGRN			character(13),
		mainOKVED		character(200),
		VKRId                       INTEGER REFERENCES VU_VK_ORG.VKR (id),
		OMSUId                    INTEGER REFERENCES VU_VK_ORG.OMSU (id),
		allwork			character(20),
		bronwork		        boolean,
		regstatus		        character(200));


:

MainTableQuery: Parametr 'maintable.fullname' not found


, , ?
19 20, 17:35[22153865]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
INSERT - . , Postgress, , .
19 20, 19:01[22153915]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
- . : ?, .
19 20, 19:15[22153927]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
.
19 20, 19:20[22153929]      |
 Re: PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
shalamyansky
- . : ?, .




, xml, .

xml , ?



xml, :

<?xml version="1.0" encoding="windows-1251" ?><EGRUL ="2020-01-01">< ="2017-02-22" ="1157847138503" ="2015-04-17" ="7811191912" ="781101001" ="" ="12300" ="   ">
  < ="    &quot; &quot;" =" &quot; &quot;">
    < ="1157847138503" ="2015-04-17" />
  </>
  <>
    < ="192029" ="78" ="780000000000954" =" 86" =" " =" 13-">
      < ="" ="-" />
      < ="" =" " />
      < ="1157847138503" ="2015-04-17" />
    </>
  </>
  < ="1157847138503" ="2015-04-17">
    < ="11" ="  " />
    < ="1157847138503" ="2015-04-17" />
  </>
  < ="7847" ="     15  -" =",191124, - ,,,,   ,  10-12 .,,">
    < ="1157847138503" ="2015-04-17" />
  </>
  < ="7811191912" ="781101001" ="2015-04-17">
    < ="7811" ="     24  -" />
    < ="6157848128520" ="2015-04-17" />
  </>
  < ="088010100088" ="2015-04-22">
    < ="088010" ="         -" />
    < ="6157848583854" ="2015-05-27" />
  </>
  < ="783004440878301" ="2015-04-20">
    < ="7830" =" 30 -       " />
    < ="6157848196390" ="2015-04-22" />
  </>
  < =" " ="10000">
    < ="1157847138503" ="2015-04-17" />
  </>
  <>
    < ="1157847138503" ="2015-04-17" />
    < ="" ="" ="" ="772165312739">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="02" ="  " =" ">
      < ="1157847138503" ="2015-04-17" />
    </>
  </>
  <>
    <>
      < ="1157847138503" ="2015-04-17" />
      < ="" ="" ="" ="772165312739">
        < ="1157847138503" ="2015-04-17" />
      </>
      < ="10000">
        <>
          <>100</>
        </>
        < ="1157847138503" ="2015-04-17" />
      </>
    </>
  </>
  <>
    < ="56.29" ="        " ="2014">
      < ="9167847014117" ="2016-07-22" />
    </>
    < ="52.61.2" =" ,        ( ,  )">
      < ="9167847014117" ="2016-07-22" />
    </>
    < ="10.13" ="        " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="10.20" ="   ,   " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="10.39" ="       " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="10.71" ="     ,     " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="46.31" ="    " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="46.31.11" ="   " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="46.32" ="     " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="46.32.3" ="       " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="46.33" ="   ,      " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="46.38" ="    ,  ,   " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.11" ="    ,  ,      " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.21" ="       " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.22" ="        " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.23" ="  ,      " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.24" ="           " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.25" ="     " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.26" ="      " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.29" ="       " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.7" ="      " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.75" ="         " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
    < ="47.91.2" =" ,     -  " ="2014">
      < ="9167847014117" ="2016-07-22" />
    </>
    < ="56.10" ="       " ="2014">
      < ="9167847014117" ="2016-07-22" />
    </>
    < ="56.21" ="       " ="2014">
      < ="9167847014117" ="2016-07-22" />
    </>
    < ="56.29.2" ="       " ="2014">
      < ="9167847014117" ="2016-07-22" />
    </>
    < ="56.30" =" " ="2014">
      < ="9167847014117" ="2016-07-22" />
    </>
    < ="73.11" ="  " ="2014">
      < ="1157847138503" ="2015-04-17" />
    </>
  </>
  < ="150000009230318" ="1157847138503" ="2015-04-17">
    < ="11201" ="(11001)  " />
    < ="7847" ="     15  -" />
    <>
      <>    </>
      <>3028309348</>
      <>2015-04-14</>
    </>
    <>
      <>11001    </>
    </>
    <>
      <>   </>
    </>
    <>
      <> </>
    </>
    <>
      <> </>
    </>
    <>
      <></>
    </>
    <>
      <></>
    </>
    <>
      <>    ٨  </>
    </>
    < ="78" ="009231537" ="2015-04-20" />
  </>
  < ="150000009584552" ="6157848128520" ="2015-04-17">
    < ="13200" ="      " />
    < ="7847" ="     15  -" />
  </>
  < ="150000009909682" ="6157848196390" ="2015-04-22">
    < ="13400" ="      " />
    < ="7847" ="     15  -" />
  </>
  < ="150000012722825" ="6157848583854" ="2015-05-27">
    < ="13300" ="      " />
    < ="7847" ="     15  -" />
  </>
  < ="150000070667068" ="9167847014117" ="2016-07-22">
    < ="12201" ="(14001)       " />
    < ="7847" ="     15  -" />
    <>
      <>14001   .,    . . (.2.1)</>
    </>
    <>
      <></>
    </>
  </>
</>
19 20, 19:32[22153936]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
? . :
dmUnit.DM.MainTableQuery.SQL.Text :='INSERT INTO VU_VK_ORG.maintable (fullname) VALUES (:NaimULPoln)';
dmUnit.DM.MainTableQuery.Params.ParamByName('NaimULPoln').Value:=XMLdocument.DocumentElement.ChildNodes[''];

:
dmUnit.DM.MainTableQuery.SQL.Text :='INSERT INTO VU_VK_ORG.maintable (fullname) VALUES (?)';
dmUnit.DM.MainTableQuery.Params.Params[0].Value:=XMLdocument.DocumentElement.ChildNodes[''];
19 20, 19:40[22153944]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
, //@. , XPath , .
19 20, 19:46[22153953]      |
 Re: PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
shalamyansky,

, ,

Unknown Param.DataType 0


-
19 20, 19:46[22153954]      |
 Re: PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
shalamyansky,

postgre :

DO $$
      DECLARE egrul_xml xml;
BEGIN

egrul_xml := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('D://EGRUL.xml'), 'UTF8'));

DROP TABLE IF EXISTS egrul_xml_tmp;
CREATE TEMP TABLE egrul_xml_tmp AS
	SELECT
		(xpath('//@', x))[1]::text AS INN,
		(xpath('///@', x))[1]::text AS NameULP,
		(xpath('///@', x))[1]::text AS NameULS,
		(xpath('////@', x))[1]::text AS IndexAdres,
		(xpath('/////@', x))[1]::text AS AdresNameReg,
		(xpath('/////@', x))[1]::text AS AdresTipReg,
		(xpath('/////@', x))[1]::text AS AdresTipSity,
		(xpath('/////@', x))[1]::text AS AdresNameSity,
		(xpath('/////@', x))[1]::text AS AdresTipStreet,
		(xpath('/////@', x))[1]::text AS AdresNameStreet,
		(xpath('////@', x))[1]::text AS AdresHome,
		(xpath('////@', x))[1]::text AS AdresKorp,
		(xpath('////@', x))[1]::text AS AdresOffice,
		(xpath('///@', x))[1]::text AS tlf,
		(xpath('///@E-mail', x))[1]::text AS email,
		(xpath('////@', x))[1]::text AS NameDDL,
		(xpath('////@', x))[1]::text AS FamDL,
		(xpath('////@', x))[1]::text AS NameDL,
		(xpath('////@', x))[1]::text AS FNameDL,
		(xpath('////@', x))[1]::text AS StatusUL,
		(xpath('////@', x))[1]::text AS PrekUL,
		(xpath('///@', x))[1]::text AS DataPUL		
		FROM unnest(xpath('//', egrul_xml)) x;
END$$;

INSERT INTO xml_egrul SELECT * FROM egrul_xml_tmp;


delphi , ,
19 20, 19:54[22153957]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
. , Delphi. , - . , , , .
19 20, 19:54[22153958]      |
 Re: PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
shalamyansky, ,
19 20, 19:59[22153960]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
Eter2020

Unknown Param.DataType 0

, . , . , , . .
19 20, 20:00[22153961]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
Eter2020

. , , , .
19 20, 20:07[22153965]      |
 Re: PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
shalamyansky,

?
19 20, 20:11[22153970]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
-. mail ru.
19 20, 20:14[22153972]      |
 Re: PostgreSQL+XML  [new]
shalamyansky
Member

:
: 156
SELECT
    (xpath('//@', x))[1]::text AS INN,
    (xpath('///@', x))[1]::text AS NameULP,
    (xpath('///@', x))[1]::text AS NameULS,
    (xpath('////@', x))[1]::text AS IndexAdres,
  FROM unnest(xpath('//', egrul_xml)) x;

Postgress XML? . Delphi ?
19 20, 21:04[22153996]      |
 Re: PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
shalamyansky,

19 20, 21:30[22154007]      |
 Re: PostgreSQL+XML  [new]
Eter2020
Member

:
: 14
, :

procedure TfImportEGRUL.Button2Click(Sender: TObject);
var
i: integer;
FName: string;
begin
     if OpenDialog1.Execute Then
     begin
      FName:=OpenDialog1.FileName;
      XMLDocument.LoadFromFile(FName);
      XMLDocument.Active := true;
      for i:=0 to XMLDocument.DocumentElement.ChildNodes.Count - 1 do
       begin
        dmUnit.DM.MainTableQuery.SQL.Clear;
        dmUnit.DM.MainTableQuery.SQL.Text :='INSERT INTO VU_VK_ORG.maintable (fullname) VALUES (:)';
        dmUnit.DM.MainTableQuery.Params.ParamByName('').Value:=XMLDocument.DocumentElement.ChildNodes[i].ChildNodes[''].GetAttribute('');
        dmUnit.DM.MainTableQuery.Open;
        end;
        dmUnit.DM.MainTableQuery.Active := True;
     end;
end;


, ,

)
19 20, 22:49[22154042]      |
 Re: PostgreSQL+XML  [new]
Dmitry Arefiev
Member

:
: 9856
demos\Object Pascal\Database\FireDAC\Samples\AddOn\VSE
19 20, 23:23[22154065]      |
 Re: PostgreSQL+XML  [new]
Fr0sT-Brutal
Member

:
: 319
1) SQL.Clear - SQL.Text; , - ExecSQL ( , .. )
2)
3) ParamByName('') - , .
4) , for-in
5) XPath - ( , ). , ( ).
      dmUnit.DM.MainTableQuery.SQL.Text := 'INSERT INTO VU_VK_ORG.maintable (fullname) VALUES (:NaimYLPoln)';
      for node in XMLDocument.DocumentElement.ChildNodes do
      begin
        dmUnit.DM.MainTableQuery.Params.ParamByName('NaimYLPoln').Value := node.ChildNodes[''].GetAttribute('');
        dmUnit.DM.MainTableQuery.ExecSQL;
      end;


: 3 20, 15:38
3 20, 15:39[22161701]      |
/ Delphi