How do I load values in txt file to mysql table colum-CodePudding

I have value like this stored in value.txt file

 <systemUsage>
    <memoryUsage>
        <memoryUsage percentOfJvmHeap="70" />
    </memoryUsage>
    <storeUsage>
        <storeUsage limit="100 gb"/>
    </storeUsage>
    <tempUsage>
        <tempUsage limit="50 gb"/>
    </tempUsage>
</systemUsage>

I have created table in the below way

  USE esb;
CREATE TABLE `esb_payload_ml` (
  `ID` varchar(50) DEFAULT NULL,
  `Payload` LONGTEXT DEFAULT NULL
  );

I am unable to find a way to insert the txt file details in payload columnn of table.

I saw load option is there but it is more of entire table oriented. I want the txt files value to be in specific column.

Any Help is widely appreciated

CodePudding user response：

declare @esb_payload_ml table (ID int, Product varchar(max))

insert into @esb_payload_ml values (1,'<systemUsage>
                <memoryUsage>
                    <memoryUsage percentOfJvmHeap="70" />
                </memoryUsage>
                <storeUsage>
                    <storeUsage limit="100 gb"/>
                </storeUsage>
                <tempUsage>
                    <tempUsage limit="50 gb"/>
                </tempUsage>
            </systemUsage>')

Select * from @esb_payload_ml

Output:

ID  Product
1   <systemUsage>                  <memoryUsage>                      <memoryUsage percentOfJvmHeap="70" />                  </memoryUsage>                  <storeUsage>                      <storeUsage limit="100 gb"/>                  </storeUsage>                  <tempUsage>                      <tempUsage limit="50 gb"/>                  </tempUsage>              </systemUsage>

CodePudding user response：

Use LOAD_FILE() (pay attention to needed account privileges and server settings values).

The function returns complete file content as binary string. For to convert it to text use CAST() function.

INSERT INTO `esb_payload_ml`
VALUES
(1, CAST(LOAD_FILE('X:\\Folder name\\value.txt') AS CHAR));

If a file contains more than one row then load it in CTE or into temptable then parse to separate values and save.