Insert in a specific position data from xml file

Question

Suppose I have this code:

EXEC [dbo].[NIACFORCOMPONENTS]
@xml=@xml,
@idniac=@idniac out

      

 DECLARE @cnt INT, @i INT;

SET @cnt = @xml.value('count(/NIACList/NIAC)', 'INT');

SET @i = 1;
WHILE @i <= @cnt
BEGIN

INSERT INTO NIACList(IDNIAC)
    SELECT 
    @idNIAC as N'@idNIAC'
     
  FROM @xml.nodes('/NIACList/NIAC[position()=sql:variable("@i")]') AS NIACList(c)

    
   SET @i += 1;
   END

   select *from NIACList 
  
END

From this insertion, I must add lots of IDNIACs to a single IDNIACList column (1->many).

But, when I execute the procedure, it shows the following result:

IDNIACList
----------
1
2
3
4
5
6

IDNIAC
----------
2
2
2
2
2
2

My wanted result would be like this:for all the insertions,1 column should always have 1, and the second to be in order (1,2,...)

Yitzhak Khabinsky · Accepted Answer · 2020-11-03 15:50:09Z

1

IMHO, I recently answered a similar question.

Please try the following conceptual example.

SQL

-- DDL and sample data population, start
-- parent table
DECLARE @state TABLE (stateID INT IDENTITY PRIMARY KEY, stateName VARCHAR(30), abbr CHAR(2), capital VARCHAR(30));
-- child table (1-to-many)
DECLARE @city TABLE (cityID INT IDENTITY PRIMARY KEY, stateID INT, city VARCHAR(30), [population] INT);
-- DDL and sample data population, end

DECLARE @xml XML =
N'<root>
   <state>
      <StateName>Florida</StateName>
      <Abbr>FL</Abbr>
      <Capital>Tallahassee</Capital>
      <cities>
         <city>
            <city>Miami</city>
            <population>470194</population>
         </city>
         <city>
            <city>Orlando</city>
            <population>285713</population>
         </city>
      </cities>
   </state>
   <state>
      <StateName>Texas</StateName>
      <Abbr>TX</Abbr>
      <Capital>Austin</Capital>
      <cities>
         <city>
            <city>Houston</city>
            <population>2100263</population>
         </city>
         <city>
            <city>Dallas</city>
            <population>5560892</population>
         </city>
      </cities>
   </state>
</root>';

DECLARE @cnt INT, @i INT;
-- to preserve unique identifier IDENTITY of a parent table
DECLARE @ParentID INT;
-- count total number of <state> elements
SET @cnt = @xml.value('count(/root/state)', 'INT');

-- loop through XML
SET @i = 1;
WHILE @i <= @cnt BEGIN
   INSERT INTO @state (stateName, abbr, capital)
   SELECT c.value('(StateName/text())[1]', 'VARCHAR(30)') AS StateName 
      , c.value('(Abbr/text())[1]', 'CHAR(2)') AS Abbr
      , c.value('(Capital/text())[1]', 'VARCHAR(30)') AS Capital
   FROM @xml.nodes('/root/state[sql:variable("@i")]') AS t(c)

   -- capture last inserted IDENTITY value for the parent table
   SET @ParentID = SCOPE_IDENTITY();

    INSERT INTO @city (stateID, city, [population])
    SELECT @ParentID -- IDENTITY from the parent table
        , c.value('(city/text())[1]', 'VARCHAR(30)') AS [population]
        , c.value('(population/text())[1]', 'INT') AS BGColor
    FROM @xml.nodes('/root/state[sql:variable("@i")]/cities/city') AS t(c)

   SET @i += 1;
END

-- test
SELECT * FROM @state;
SELECT * FROM @city;

answered Nov 3, 2020 at 15:50

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dan morcov Over a year ago

Yeah, it works, I'm sorry for asking so multiple times the same thing,the problem is that I didn't formulated correctly my question.Based on your example, I've tried the same and it works, but I want to be paginated the inserted ids from NIACs, based on counting the nodes.For ex:when I execute SP, using 6 nodes with data, in the NIACList table shows for each row in the IDNIAC column only value 6(that is the number of counted nodes in total), but somehow i want them to be in a order after insert(1-1,2-2,3-3). Im also sorry if I didn't understand better.

Yitzhak Khabinsky Over a year ago

@danmorcov, glad to hear that the proposed solution is working for you. Please connect with me on LinkedIn.

dan morcov Over a year ago

I'm sorry for this question, but how can I do the same process for incremeting value, now for 11 columns with ids, not only one. I take ids as parameters using scope_identity() from other tables, and then i must assign them to the insert clause.While I was using the while loop that you provided me, it worked for one column, but I don't know how to make the same action for the rest of the columns inside the insert operation(I don't even know if its possible in general).

Yitzhak Khabinsky Over a year ago

@danmorcov, you need to provide a minimal reproducible example always. Please connect with me on LinkedIn.

Collectives™ on Stack Overflow

Insert in a specific position data from xml file

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related