question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked YitzhakKhabinsky-0887 edited

XML into primary and seconday table

Hi,

I have a xml data like below

<Activity>
<ID>2</ID>
<Start>01/01/2012 13:00</Start>
<Quantity>1</Quantity>
<Observation>
<Code>14</Code>
<Value>5</Value>
</Observation>
<Observation>
<Code>76</Code>
<Value>5</Value>
</Observation>
</Activity>

<Activity>
<ID>3</ID>
<Start>01/01/2012 13:00</Start>
<Quantity>1</Quantity>
<Observation>
<Code>14</Code>
<Value>10</Value>
</Observation>
</Activity>

<Activity>
<ID>4</ID>
<Start>01/01/2012 13:00</Start>
<Quantity>1</Quantity>
</Activity>

I have two table like 'Activity' as primary table and 'Observation' as a foreign key table.
How to read the above xml and insert into these two tables?

Thanks

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@kasimmohamed-6993,

The XML provided is not well-formed.
Two options:

  • A root node is missing. You need to fix it.

  • Each <Activity>...</Activity> fragment is processed separately, Is it a correct assumption?

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered kasimmohamed-6993 commented

Check an example:

 declare @Activity table (id int not null primary key, Start datetime, Quantity int)
 declare @Observation table( activity_id int, Code int, Value int )
    
 declare @example varchar(max) ='
 <Activity>
     <ID>2</ID>
     <Start>01/01/2012 13:00</Start>
     <Quantity>1</Quantity>
     <Observation>
         <Code>14</Code>
         <Value>5</Value>
     </Observation>
     <Observation>
         <Code>76</Code>
         <Value>5</Value>
     </Observation>
 </Activity>
    
 <Activity>
     <ID>3</ID>
     <Start>01/01/2012 14:00</Start>
     <Quantity>1</Quantity>
     <Observation>
         <Code>14</Code>
         <Value>10</Value>
     </Observation>
 </Activity>
    
 <Activity>
     <ID>4</ID>
     <Start>01/01/2012 15:00</Start>
     <Quantity>1</Quantity>
 </Activity>'
    
 declare @xml xml = cast(@example as xml)
    
 insert @Activity (id, Start, Quantity)
 select 
     a.value('ID[1]', 'int'),
     a.value('Start[1]', 'datetime'),
     a.value('Quantity[1]', 'int')
 from @xml.nodes('/Activity') t(a)
    
 insert @Observation (activity_id, Code, Value)
 select 
     o.value('../ID[1]', 'int'),
     o.value('Code[1]', 'int'),
     o.value('Value[1]', 'int')
 from @xml.nodes('/Activity/Observation') t(o)
    
 select * from @Activity
 select * from @Observation
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Viorel-1

Thank you verymuch

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @kasimmohamed-6993,

Please try the following solution.
I took a rout when all <Activity>...<Activity>fragments are coming in one shot.

SQL

 -- DDL and sample data population, start
 USE tempdb;
 GO
    
 DROP TABLE IF EXISTS #Observation;
 DROP TABLE IF EXISTS #Activity;
    
 -- parent table
 CREATE TABLE #Activity  (
    ActivityID INT PRIMARY KEY, 
    [Start] DATETIME, 
    Quantity INT, 
 );
 -- child table (1-to-many)
 CREATE TABLE #Observation (
    ObservationID INT IDENTITY, 
    ActivityID INT NOT NULL FOREIGN KEY REFERENCES #Activity(ActivityID), 
    Code INT, 
    [Value] INT,
    PRIMARY KEY (ObservationID, ActivityID)
 );
    
 DECLARE @xml XML =
 N'<root>
     <Activity>
         <ID>2</ID>
         <Start>01/01/2012 13:00</Start>
         <Quantity>1</Quantity>
         <Observation>
             <Code>14</Code>
             <Value>5</Value>
         </Observation>
         <Observation>
             <Code>76</Code>
             <Value>5</Value>
         </Observation>
     </Activity>
     <Activity>
         <ID>3</ID>
         <Start>01/01/2012 13:00</Start>
         <Quantity>1</Quantity>
         <Observation>
             <Code>14</Code>
             <Value>10</Value>
         </Observation>
     </Activity>
     <Activity>
         <ID>4</ID>
         <Start>01/01/2012 13:00</Start>
         <Quantity>1</Quantity>
     </Activity>
 </root>';
 -- DDL and sample data population, end
    
 INSERT INTO #Activity (ActivityID, [Start], Quantity)
 SELECT ActivityID = p.value('(ID/text())[1]', 'INT')
     , [Start] = p.value('(Start/text())[1]', 'DATETIME')
     , Quantity = p.value('(Quantity/text())[1]', 'INT')
 FROM   @xml.nodes('/root/Activity') AS t(p);
    
 INSERT INTO #Observation (ActivityID, Code, [Value])
 SELECT ActivityID = p.value('(ID/text())[1]', 'INT')
     , Code = c.value('(Code/text())[1]', 'INT')
     , [Value] = c.value('(Value/text())[1]', 'INT')
 FROM   @xml.nodes('/root/Activity') AS t1(p)
     CROSS APPLY t1.p.nodes('Observation') AS t2(c);
    
 -- test
 SELECT * FROM #Activity;
 SELECT * FROM #Observation;

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi YitzhakKhabinsky-0887

Thanks you very much

0 Votes 0 ·

@kasimmohamed-6993,

Good to hear from you.
What was the reason to prefer Viorel's solution?

It has performance issues on two counts:

  • Navigating upstream: o.value('../ID[1]', 'int')

  • Missing text() for each and every data element.


0 Votes 0 ·