-2

I have a table:

CREATE TABLE [Course].[Navigation](
    [NavigationId] [int] IDENTITY(1,1) NOT NULL,
    ParentID int,
    ChildID int, 
    [Type] [varchar](100) NULL,
    [Label] [int] NULL,
    [Id] [varchar](100) NULL 
)

Which represents a navigational chart for a course.

The XML that I'm trying to load is:

<navigation>
<content label="Introduction">
<content label="Intro" id="000286_1001"/>
</content>
<content label="Premature Atrial Contractions">
<content label="Description" id="000286_1002"/>
<content label="ECG Features" id="000286_1003"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1004"/>
</content>
<content label="Wandering">
<content label="Description" id="000286_1005"/>
<content label="ECG Features" id="000286_1006"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1007"/>
</content>
<content label="Ectopic Atrial Rhythm">
<content label="Description" id="000286_1008"/>
<content label="ECG Features" id="000286_1009"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1010"/>
</content>
<content label="Supraventricular Tachycardia">
<content label="SVT" id="000286_1011"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1012"/>
<content label="Multifocal Atrial Tachycardia">
<content label="Description" id="000286_1013"/>
<content label="ECG Features" id="000286_1014"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1015"/>
</content>
<content label="Ectopic Atrial Tachycardia">
<content label="Description" id="000286_1016"/>
<content label="ECG Features" id="000286_1017"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1018"/>
</content>
<content label="Ectopic Atrial Tachycardia with Block">
<content label="Description" id="000286_1019"/>
</content>
<content label="Atrial Flutter">
<content label="Description" id="000286_1020"/>
<content label="ECG Features" id="000286_1021"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1022"/>
<content label="Treatment" id="000286_1023"/>
</content>
<content label="Atrial Fibrillation">
<content label="Description" id="000286_1024"/>
<content label="ECG Features" id="000286_1025"/>
<content label="Etiology/Symptoms/Treatment" id="000286_1026"/>
<content label="Signs" id="000286_1027"/>
<content label="Treatment" id="000286_1028"/>
</content>
<content label="AVRT">
<content label="Description" id="000286_1029"/>
<content label="Pathophysiology" id="000286_1030"/>
<content label="ECG Features" id="000286_1031"/>
<content label="Etiology" id="000286_1032"/>
</content>
<content label="AVNRT">
<content label="Description" id="000286_1033"/>
<content label="ECG Features" id="000286_1034"/>
<content label="Etiology" id="000286_1035"/>
</content>
<content label="Treatment" id="000286_1036"/>
</content>
<content label="Review">
<content label="Quick Quiz" id="000286_1037"/>
</content>
<content label="Conclusion">
<content label="Summary" id="000286_1038"/>
</content>
</navigation>  
  • There are lots of Transact-SQL examples of parsing XML, including hierarchical XML, around. Perhaps you are trying to achieve a specific output that the examples you've found couldn't help you with. The more you elaborate on that, as well as on what exactly you've already tried, the more likely you are to get a helpful answer. – Andriy M May 16 '17 at 20:09
1

First and foremost:

Forget about to use ChildId, keep in mind that one record can hold more than one child.

I'd suggest to use a table schema similar to next:

CREATE TABLE [Navigation]
(
    [NavigationId] [int] IDENTITY(1,1) NOT NULL,
    ParentID int,
    [Type] [varchar] (100) NULL,
    [Label] [varchar](100) NULL,
    [Id] [varchar](100) NULL,
    [LevelOrder] varchar(100)
);

I'll show you later how I used LevelOrder. As I said in my previous answer IMHO this question requires an iterative and recursive solution.

I've created this stored procedure:

CREATE PROCEDURE dbo.getXmlHierarchy(@Xml xml, @Level int, @ParentId int, @ParentRN varchar(100))
AS
BEGIN

    DECLARE @Label varchar(100) = '',
            @Id varchar(100) = '',
            @Content xml,
            @CurrentId int = 0,
            @NextLevel int,
            @RN int = 1,
            @CurrentRN varchar(100);

    DECLARE curxml CURSOR LOCAL FOR
    SELECT      l1.txt.value('@label', 'VARCHAR(100)'),
                COALESCE(l1.txt.value('@id', 'VARCHAR(100)'), ''),
                l1.txt.query('./*') content
    FROM        @Xml.nodes('content') as l1 (txt);

    OPEN curxml;

    FETCH NEXT FROM curxml INTO @Label, @Id, @Content

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @CurrentRN = CONCAT(@ParentRN, '/', 
                                REPLICATE('0', 3 - LEN(CAST(@RN AS VARCHAR(100)))),
                                CAST(@RN AS VARCHAR(100)));

        INSERT INTO Navigation (ParentId, [Type], Label, Id, LevelOrder)
        VALUES (@ParentId, 
                CONCAT('Level ', 
                CAST(@Level as VARCHAR(10))), 
                @Label, 
                @Id, 
                @CurrentRN);

        SET @CurrentId = SCOPE_IDENTITY();
        SET @NextLevel = @Level + 1;
        SET @RN = @RN + 1;

        IF CAST(@Content AS VARCHAR(MAX)) <> ''
        BEGIN
            EXEC getXmlHierarchy @Content, @NextLevel, @CurrentId, @CurrentRN
        END

        FETCH NEXT FROM curxml INTO @Label, @Id, @Content
    END

    CLOSE curxml;
    DEALLOCATE curxml;
END

How does it work

Basically it iterates through '' tags, inserting records one by one and, if current record has a child <content>, call itself recursively.

Keep in mind that to recursively call an stored procedure that uses a cursor, you must declare the cursor as LOCAL.

This procedure works for <content> tags so, you must get xml content after <navigation> tag before to call it.

DECLARE @XmlNav xml;
SELECT @XmlNav = xmlcol.query('navigation/*') FROM xmltab;

EXEC getXmlHierarchy @XmlNav, 1, 0, '';

And this is the final result:

SELECT NavigationId NavId, ParentId, Label, Id, LevelOrder
FROM Navigation ORDER BY LevelOrder;
GO
NavId | ParentId | Label                                 | Id          | LevelOrder  
----: | -------: | :------------------------------------ | :---------- | :-----------
    1 |        0 | Introduction                          |             | /001        
    2 |        1 | Intro                                 | 000286_1001 | /001/001    
    3 |        0 | Premature Atrial Contractions         |             | /002        
    4 |        3 | Description                           | 000286_1002 | /002/001    
    5 |        3 | ECG Features                          | 000286_1003 | /002/002    
    6 |        3 | Etiology/Symptoms/Treatment           | 000286_1004 | /002/003    
    7 |        0 | Wandering                             |             | /003        
    8 |        7 | Description                           | 000286_1005 | /003/001    
    9 |        7 | ECG Features                          | 000286_1006 | /003/002    
   10 |        7 | Etiology/Symptoms/Treatment           | 000286_1007 | /003/003    
   11 |        0 | Ectopic Atrial Rhythm                 |             | /004        
   12 |       11 | Description                           | 000286_1008 | /004/001    
   13 |       11 | ECG Features                          | 000286_1009 | /004/002    
   14 |       11 | Etiology/Symptoms/Treatment           | 000286_1010 | /004/003    
   15 |        0 | Supraventricular Tachycardia          |             | /005        
   16 |       15 | SVT                                   | 000286_1011 | /005/001    
   17 |       15 | Etiology/Symptoms/Treatment           | 000286_1012 | /005/002    
   18 |       15 | Multifocal Atrial Tachycardia         |             | /005/003    
   19 |       18 | Description                           | 000286_1013 | /005/003/001
   20 |       18 | ECG Features                          | 000286_1014 | /005/003/002
   21 |       18 | Etiology/Symptoms/Treatment           | 000286_1015 | /005/003/003
   22 |       15 | Ectopic Atrial Tachycardia            |             | /005/004    
   23 |       22 | Description                           | 000286_1016 | /005/004/001
   24 |       22 | ECG Features                          | 000286_1017 | /005/004/002
   25 |       22 | Etiology/Symptoms/Treatment           | 000286_1018 | /005/004/003
   26 |       15 | Ectopic Atrial Tachycardia with Block |             | /005/005    
   27 |       26 | Description                           | 000286_1019 | /005/005/001
   28 |       15 | Atrial Flutter                        |             | /005/006    
   29 |       28 | Description                           | 000286_1020 | /005/006/001
   30 |       28 | ECG Features                          | 000286_1021 | /005/006/002
   31 |       28 | Etiology/Symptoms/Treatment           | 000286_1022 | /005/006/003
   32 |       28 | Treatment                             | 000286_1023 | /005/006/004
   33 |       15 | Atrial Fibrillation                   |             | /005/007    
   34 |       33 | Description                           | 000286_1024 | /005/007/001
   35 |       33 | ECG Features                          | 000286_1025 | /005/007/002
   36 |       33 | Etiology/Symptoms/Treatment           | 000286_1026 | /005/007/003
   37 |       33 | Signs                                 | 000286_1027 | /005/007/004
   38 |       33 | Treatment                             | 000286_1028 | /005/007/005
   39 |       15 | AVRT                                  |             | /005/008    
   40 |       39 | Description                           | 000286_1029 | /005/008/001
   41 |       39 | Pathophysiology                       | 000286_1030 | /005/008/002
   42 |       39 | ECG Features                          | 000286_1031 | /005/008/003
   43 |       39 | Etiology                              | 000286_1032 | /005/008/004
   44 |       15 | AVNRT                                 |             | /005/009    
   45 |       44 | Description                           | 000286_1033 | /005/009/001
   46 |       44 | ECG Features                          | 000286_1034 | /005/009/002
   47 |       44 | Etiology                              | 000286_1035 | /005/009/003
   48 |       15 | Treatment                             | 000286_1036 | /005/010    
   49 |        0 | Review                                |             | /006        
   50 |       49 | Quick Quiz                            | 000286_1037 | /006/001    
   51 |        0 | Conclusion                            |             | /007        
   52 |       51 | Summary                               | 000286_1038 | /007/001    

As you can see, you can use LevlOrder as an hierarchical field to query or to order the result set.

dbfiddle here

0

You can do this with a loop one level at a time.
Use merge with output to capture the generated NavigationId along with the XML that will have that id as a ParentId.

declare @X xml = '
<navigation>
  <!-- XML goes here -->
</navigation>';

declare @Level int = 1;
declare @T table(ParentID int, Content xml, Level int);
insert into @T(Content, Level) values(@X.query('/navigation/content'), 1);

while 1 = 1
begin
  merge dbo.Navigation as T
  using (
        select T.ParentID,
               C.X.value('@label', 'varchar(100)') as Label,
               C.X.value('@id', 'varchar(100)') as Id,
               C.X.query('content') as Content
        from @T as T
          cross apply T.Content.nodes('content') as C(X)
        where T.level = @Level
        ) as S
  on 0 = 1
  when not matched by target then
    insert (ParentID, Label, Id)
      values(S.ParentID, S.Label, S.Id)
  output inserted.NavigationId,
         S.Content,
         @Level + 1
    into @T(ParentID, Content, Level);

  if @@rowcount = 0
    break;

  set @Level += 1;

end;

DBFiddle

Ref: Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.