esql command to insert query into data base using xml format

Question

I tried to insert a sql query using esql code:

INSERT INTO Database.dbo.CUSTOMERS Values (9330,'Sai',7);

It is working fine but it was show error when it tried to insert code using xml format like:

INSERT INTO Database.dbo.CUSTOMERS(ID,NAME,AGE) Values (InputRoot.XMLNSC.emps.emp.id,InputRoot.XMLNSC.emps.emp.name,InputRoot.XMLNSC.emps.emp.age);

Then it was showing errors like BIP2230E, BIP2488E, BIP2321E.

If there is any connectivity problem means first insert command also should not work. Select also working fine.

Any suggestions to resolve problem?

Answer 1

It is fairly obvious that your paths (InputRoot.XMLNSC.emps.emp.id, etc ) do not exist under InputRoot.XMLNSC. You could easily check this using the debugger or (better) a Trace node. To fix the problem, correct those paths.

You should also be declaring and using a REFERENCE variable, to make your ESQL more readable:

-- This is not the correct path, otherwise your code would be working already!
DECLARE refEmp REFERENCE to InputRoot.XMLNSC.emps.emp;
INSERT 
    INTO Database.dbo.CUSTOMERS(ID,NAME,AGE) 
    VALUES (refEmp.id,refEmp.name,refEmp.age)