Sql-server – good way to link tables and sequences

...is this a good way to store these data?

No, this is probably the worst way to store this data if you want to query it effectively. You need to study some database normalisation examples before you go any further with this

This is a very quick sketch up of a possible schema that could be used. Incomplete and untested but it should be clear how this maps to your XML fragment.

CREATE TABLE dbo.Customer
(
    CustomerId INT NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId ASC)

CREATE TABLE dbo.Employee
(
    EmployeeNumber VARCHAR(10) NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNumber ASC)

CREATE TABLE dbo.Visit
(
    VisitId INT NOT NULL
    , VisitDate DATETIME
    , CustomerId INT
    , EmployeeNumber VARCHAR(10)
)

ALTER TABLE dbo.Visit
ADD CONSTRAINT PK_Visit PRIMARY KEY (VisitId ASC)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Customer] FOREIGN KEY(CustomerId)
REFERENCES dbo.Customer (CustomerId)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Employee] FOREIGN KEY(EmployeeNumber)
REFERENCES dbo.Employee (EmployeeNumber)

You can update a join in Oracle if the following conditions are met:

1. Only one base table is updated
2. All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

• make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
• use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).

• review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

  UPDATE (SELECT mac.in_use, mac.update_time
            FROM pg_machine mac
           WHERE mac.lablocid IN (SELECT loc.lablocid 
                                    FROM pg_lablocation loc 
                                   WHERE loc.dnsname = 'value')
             AND to_date('02-JAN-2013') > mac.update_time)
     SET in_use = 1 - MOD(101, 2), 
         update_time = to_date('02-JAN-2013');
  

  This could be rewritten as:

  UPDATE pg_machine mac
     SET in_use = 1 - MOD(101, 2), 
         update_time = to_date('02-JAN-2013')
   WHERE mac.lablocid IN (SELECT loc.lablocid 
                            FROM pg_lablocation loc 
                           WHERE loc.dnsname = 'value')
     AND to_date('02-JAN-2013') > mac.update_time;
  

In this case I would go with the third option: in general you can't update the parent in a parent-child join.