SQLTeam.com | Weblogs | Forums

Need Autonumber in CTE For Hirarchial XML data

sql2012

#1

Hi,

I have a situation where I am extracting each node value from Hirarchial XML and I need autonumber to be generated against each node. But it is not getting generated correctly. I even tried using row_number but since xml does not have any incremental column, so numbering is not generated correctly.

I need first column to be free flow integer number from 1.....to .N.
My apologies for this long message but did not find option to attach xml and result separately.

This is very urgent for me. Any help regarding this will be highly appreciated. Thanks in advance.

SQL Query

;WITH XMLNAMESPACES(DEFAULT N'http://www.imsglobal.org/xsd/imscp_v1p1') ,

Hierarchy ([Counter],[Level], [ParentName], [Title], [Identifier], [PageReference], [Children])
AS
(
SELECT
row_number() over ( order by [Par].value('(@identifier[1])','varchar(100)') ),
0 as [Level],
CONVERT(varchar(100), NULL) AS [ParentName],
[Par].value('(title/text())[1]','varchar(500)') AS [Title],
[Par].value('(@identifier[1])','varchar(100)') AS [Identifier],
[Par].value('(@identifierref[1])','varchar(200)') AS [PageReference],
[Par].query('./') AS [Children]
FROM @xml.nodes('manifest/organizations/organization/item') Parents([Par])
UNION ALL
SELECT
ROW_NUMBER() over (order by [Level]),
Level + 1 as [Level] ,
h.[Identifier] AS [ParentName],
[NL].value('(title/text())[1]','varchar(500)') AS [Title],
[NL].value('(@identifier[1])','varchar(100)') AS [Identifier],
[NL].value('(@identifierref[1])','varchar(200)') AS [PageReference],
[NL].query('./
') AS [Children]

FROM Hierarchy h
    CROSS APPLY h.Children.nodes('./item') NextLevel([NL])

)

SELECT [Counter],

[Level],   
[ParentName], 
[Title], 
[Identifier],
[PageReference] 

FROM Hierarchy
ORDER BY [Level]

XML

<manifest xmlns="http://www.imsglobal.org/xsd/imscp_v1p1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:adlcp="http://www.adlnet.org/xsd/adlcp_v1p3" xmlns:adlseq="http://www.adlnet.org/xsd/adlseq_v1p3" xmlns:adlnav="http://www.adlnet.org/xsd/adlnav_v1p3" xmlns:imsss="http://www.imsglobal.org/xsd/imsss" xsi:schemaLocation="http://www.imsglobal.org/xsd/imscp_v1p1              imscp_v1p1.xsd    http://www.adlnet.org/xsd/adlcp_v1p3 adlcp_v1p3.xsd              http://www.adlnet.org/xsd/adlseq_v1p3    adlseq_v1p3.xsd              http://www.adlnet.org/xsd/adlnav_v1p3 adlnav_v1p3.xsd    http://www.imsglobal.org/xsd/imsss imsss_v1p0.xsd" identifier="SSc7f9dfdf-fb80-47ed-aa5e-2fe669acb4fd">
  <metadata>
    <CourseCode>8000</CourseCode>
    <JulianDate>07-06-15</JulianDate>
    <schema>ADL SCORM</schema>
    <schemaversion>2004 3rd Edition</schemaversion>
    <lms>Default</lms>
    <adlcp:location>metadata_course.xml</adlcp:location>
  </metadata>
  <organizations default="defaultOrganization">
    <organization identifier="defaultOrganization">
      <title>eBooks Features Sampler Course</title>
      <item identifier="Item1" isvisible="true">
        <title>Introduction</title>
        <item identifier="Item1.1" identifierref="R_8fcc4b01-6743-4ca3-b681-fcd518b84a07" isvisible="true">
          <title>Cover Page</title>
          <imsss:sequencing>
            <imsss:rollupRules objectiveMeasureWeight="0" />
          </imsss:sequencing>
        </item>
        <imsss:sequencing>
          <imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" />
          <imsss:rollupRules objectiveMeasureWeight="0">
            <imsss:rollupRule childActivitySet="all">
              <imsss:rollupConditions>
                <imsss:rollupCondition condition="completed" />
              </imsss:rollupConditions>
              <imsss:rollupAction action="satisfied" />
            </imsss:rollupRule>
          </imsss:rollupRules>
        </imsss:sequencing>
      </item>
      <item identifier="Item2" isvisible="true">
        <title>Parablock Features</title>
        <item identifier="Item2.1" identifierref="R_776215d7-5a21-41b4-9722-7b35127ea40a" isvisible="true">
          <title>Audio</title>
          <imsss:sequencing>
            <imsss:rollupRules objectiveMeasureWeight="0" />
          </imsss:sequencing>
        </item>
        <item identifier="Item2.2" identifierref="R_638ea958-c830-4360-8220-149ef03842ea" isvisible="true">
          <title>Bounding Rectangle</title>
          <imsss:sequencing>
            <imsss:rollupRules objectiveMeasureWeight="0" />
          </imsss:sequencing>
        </item>
        <item identifier="Item2.3" identifierref="R_00b86664-0f14-4704-ae51-cf1d738af877" isvisible="true">
          <title>Categories</title>
          <imsss:sequencing>
            <imsss:rollupRules objectiveMeasureWeight="0" />
          </imsss:sequencing>
        </item>
      </item>
      </organization>
  </organizations>
</manifest>

Result
1 0 NULL Introduction Item1 NULL
2 0 NULL Parablock Features Item2 NULL
3 0 NULL InLine Features Item3 NULL
4 0 NULL Question Block Features Item4 NULL
1 1 Item4 Bin Drop Item4.1 R_6a7fd3e1-ecfe-4ea3-a18d-27e24ed4cc2a
2 1 Item4 Drag and Drop Item4.2 R_e50dfe37-f6c3-4723-98b2-6832d7af0732
3 1 Item4 Essay Activity Item4.3 R_e70732a3-4d28-4808-b1dd-3360ab3797da
1 1 Item1 Cover Page Item1.1 R_8fcc4b01-6743-4ca3-b681-fcd518b84a07
1 1 Item3 Autonumber Item3.1 R_199cc06b-8ac8-4608-942a-1489c74a64ac
2 1 Item3 Citation Item3.2 R_5c1ed0d6-a8e1-438a-8a9f-a6356f1ad659


#2

When I run your query with the XML you posted, what I get is not the same as the result that you are getting.

The following is what I am seeing. What would this result look like if it were to be ordered as you desire?

Counter	Level	ParentName	Title			Identifier	PageReference
1	0	NULL		Introduction		Item1	NULL
2	0	NULL		Parablock Features	Item2	NULL
1	1	Item2		Audio			Item2.1	R_776215d7-5a21-41b4-9722-7b35127ea40a
2	1	Item2		Bounding Rectangle	Item2.2	R_638ea958-c830-4360-8220-149ef03842ea
3	1	Item2		Categories		Item2.3	R_00b86664-0f14-4704-ae51-cf1d738af877
1	1	Item1		Cover Page		Item1.1	R_8fcc4b01-6743-4ca3-b681-fcd518b84a07

#3

James...First column 'Counter' should return a unique incremented that i can use further as row number or record number. Currently it starts from 1 again moment a new level is encountered.

Requirement here is that i need a incremental numbering counter in column one (that should start from 1 till number of records in table). I hope that clarifies my requirement and situation.


#5

I have shortened xml to reduce space consumption.
Output should be as per first come first first show basis means first item 1 and its innerchildren should be listed and then item 2 and its inner children. Secondly - 1st column 'counter' should show just an incremental value based on number of records returned in result irrespective of levels like from 1,2,3,4,5,6,....and so on:

Counter	Level	ParentName	Title			Identifier	PageReference
1	0	NULL		Introduction		Item1	NULL
2	1	Item1		Cover Page		Item1.1	R_8fcc4b01-6743-4ca3-b681-fcd518b84a07
3	0	NULL		Parablock Features	Item2	NULL
4	1	Item2		Audio			Item2.1	R_776215d7-5a21-41b4-9722-7b35127ea40a
5	1	Item2		Bounding Rectangle	Item2.2	R_638ea958-c830-4360-8220-149ef03842ea
6	1	Item2		Categories		Item2.3	R_00b86664-0f14-4704-ae51-cf1d738af877

#6

Column SN in the output of this query

;WITH XMLNAMESPACES(DEFAULT N'http://www.imsglobal.org/xsd/imscp_v1p1') ,

Hierarchy ([Counter],[Level], [ParentName], [Title], [Identifier], [PageReference], [Children],N)
AS
(
    SELECT  ROW_NUMBER() OVER ( ORDER BY [Par].value('(@identifier[1])',
                                                     'varchar(100)') ) ,
            0 AS [Level] ,
            CONVERT(VARCHAR(100), NULL) AS [ParentName] ,
            [Par].value('(title/text())[1]', 'varchar(500)') AS [Title] ,
            [Par].value('(@identifier[1])', 'varchar(100)') AS [Identifier] ,
            [Par].value('(@identifierref[1])', 'varchar(200)') AS [PageReference] ,
            [Par].query('./*') AS [Children],
            CAST( ROW_NUMBER() OVER (ORDER BY [Par].value('(@identifier[1])','varchar(100)') ) AS varchar(32)) AS N
    FROM    @xml.nodes('manifest/organizations/organization/item') Parents ( [Par] )
UNION ALL
SELECT  ROW_NUMBER() OVER ( ORDER BY [Level] ) ,
        Level + 1 AS [Level] ,
        h.[Identifier] AS [ParentName] ,
        [NL].value('(title/text())[1]', 'varchar(500)') AS [Title] ,
        [NL].value('(@identifier[1])', 'varchar(100)') AS [Identifier] ,
        [NL].value('(@identifierref[1])', 'varchar(200)') AS [PageReference] ,
        [NL].query('./*') AS [Children],
        CAST(N + CAST( ROW_NUMBER() OVER (ORDER BY [NL].value('(@identifier[1])','varchar(100)') ) AS varchar(32))
			AS VARCHAR(32)) AS N
FROM    Hierarchy h
        CROSS APPLY h.Children.nodes('./item') NextLevel ( [NL] )
)

SELECT  [Counter] ,
        [Level] ,
        [ParentName] ,
        [Title] ,
        [Identifier] ,
        [PageReference],
        ROW_NUMBER() OVER (ORDER BY N) AS SN
FROM    Hierarchy
ORDER BY ROW_NUMBER() OVER (ORDER BY N);

#7

awesome solution JamesK.
You made my day. Thanks again.

Just to understand -
-You first added values in N column as string and then sorted it with another column and

  • implemented row_number to the same.

Can you help me in understanding below piece:
ROW_NUMBER() OVER (ORDER BY [NL].value('(@identifier[1])

Will it generate number as per whole recordset?


#8

The query is relying on string sort. In string sort, if you have 3 items - for example, 1, 2, and 11, the sorting order will be 1, 11, 2. That is, 11 is lower than 2. This is because string sort does the sorting character by character, starting with the leftmost.

In the anchor part of the query, N is the sequence number (as a character string) of the top level nodes. In the recursive part, N appends the sequence number to the sequence number of its parent node. You can see what it does if you add column N to the final SELECT.

Now that I am looking at your other columns, you could even eliminate all that stuff I added, and just have a
ROW_NUMBER() OVER (ORDER BY Identifier) to get the result you want.


#9

Thanks James for explanation.

I tried with just ROW_NUMBER() OVER (ORDER BY Identifier) after removing your part and it is showing up counting but order is not correct completely.

So I will go with your previous approach as that resolved the issue. :smile:
Thanks Again.


#10

I just noticed...it is giving problem in some cases...adding result below and will send complete xml in separate message due to size issue

0    NULL    Introduction    Item1    NULL    1
1    Item1    Cover Page    Item1.1    R_8fcc4b01-6743-4ca3-b681-fcd518b84a07    2
1    Item1    Resources    Item1.2    R_84dfd9ed-2b45-4db3-accf-acde65edc3e1    3
0    NULL    Parablock Features    Item2    NULL    4
1    Item2    List    FolderOfItem2.13    NULL    5
1    Item2    Margin Note    Item2.14    R_a21f68ea-5683-4a0d-b01b-83e3cf712c70    6
1    Item2    Quote Block    Item2.16    R_2c12998c-9c30-4996-856f-4d0f1ab07ff5    7
2    FolderOfItem2.13    List    Item2.13    R_630f40b2-1937-4b66-8fed-bdb2deda3138    8
2    FolderOfItem2.13    List Marker Time!    Item2.13.2    R_58f80740-ccfc-41f4-ab9f-1438143d301c    9
1    Item2    Table    Item2.17    R_27aff339-7a58-4606-87f2-5b0971a91f37    10
1    Item2    Titled Block    Item2.19    R_d6893207-3d5a-4ba1-903f-efbf7f677a76    11
2    FolderOfItem2.13    New Bullet Styles    Item2.13.3    R_781e9c18-5e81-4274-9894-78746762771f    12
1    Item2    Bounding Rectangle    Item2.2    R_638ea958-c830-4360-8220-149ef03842ea    13
1    Item2    XML Slide    Item2.20    R_f36ad2c0-0dc0-46a1-9d75-389eede9707d    14
1    Item2    Not Tested    Item2.21    R_de8215ee-2ff9-4918-9d8b-03bab00092e3    15
1    Item2    Categories    Item2.3    R_00b86664-0f14-4704-ae51-cf1d738af877    16
1    Item2    Custom Note    Item2.4    R_a3fb3b0f-e110-4b35-a7f6-a93c0e480dd8    17
1    Item2    Doc Writer    Item2.5    R_ef3f793e-2e36-452b-9727-473a30d4533e    18
1    Item2    Movie Object    FolderOfItem2.15    NULL    19
1    Item2    Equation    Item2.6    R_a9e0e5af-390e-4481-b801-386fcb0d8bdf    20
1    Item2    Flash Cards    Item2.9    R_2c167a85-3d25-4307-9697-c055b643241f    21
2    FolderOfItem2.15    Movie Object    Item2.15    R_5e5394f1-d002-432c-b633-e98d81d8f495    22
2    FolderOfItem2.15    Movie Object with Embed Code    Item2.15.2    R_5969d8e3-1924-4dd8-b7c9-c3b2773ea439    23
2    FolderOfItem2.15    Movie Object with Local File    Item2.15.3    R_47e3b4a9-51fb-4469-a5d0-f56b18f9d4b2    24
1    Item2    Tabs    FolderOfItem2.18    NULL    25
2    FolderOfItem2.18    Tabs    Item2.18    R_8b865f3d-1555-4ffc-a056-015b4e2a72f8    26
2    FolderOfItem2.18    Vertical Tabs    Item2.18.2    R_c4b22bb1-4e31-470c-875b-0f70da326a6c    27
2    FolderOfItem2.18    Horizontal Tabs    Item2.18.3    R_ef6b959f-6f84-457d-a782-8d8489b9d4cb    28
1    Item2    Figures    FolderOfItem2.7    NULL    29
2    FolderOfItem2.7    Figures    Item2.7    R_5bbdc574-6795-429e-9850-8d4cdd3c7a98    30
2    FolderOfItem2.7    In the Content Area    Item2.7.2    R_ec3c782c-6a73-4889-827a-0098a4580a95    31
2    FolderOfItem2.7    In the Banner    Item2.7.3    R_538cd6df-a659-45d8-8272-707a66ca107e    32
2    FolderOfItem2.7    Diagram    Item2.7.4    R_454bbb5e-b32d-4e68-8f36-2e01dcabfffe    33
1    Item2    Filter Metadata    FolderOfItem2.8    NULL    34
2    FolderOfItem2.8    Filter Metadata    Item2.8    R_2347a6c1-cb16-4275-a2ef-f640d9ec069a    35
2    FolderOfItem2.8    Topic Filtered for Instructor    Item2.8.2    R_22b1dd74-255d-4b86-95a7-6faa01d0ff1f    36
2    FolderOfItem2.8    Topic Filtered for Student    Item2.8.3    R_882c0ec0-c367-4429-a480-5f6c29e89fe5    37
1    Item2    Audio    Item2.1    R_776215d7-5a21-41b4-9722-7b35127ea40a    38
1    Item2    Glossary Item    Item2.10    R_513f7866-6879-45eb-98e3-d2babfcf22dd    39
1    Item2    Icon    Item2.11    R_aaaf2f0c-a5a6-4968-807c-2498276e74dd    40
1    Item2    Image Map    Item2.12    R_a9c10dc6-6b14-4c1a-b61d-c23ec2b73b57    41
0    NULL    InLine Features    Item3    NULL    42
1    Item3    Autonumber    Item3.1    R_199cc06b-8ac8-4608-942a-1489c74a64ac    43
1    Item3    Filter Metadata    Item3.4    R_8e2e4382-7df0-472a-86f6-a93504b45714    44
1    Item3    Footnote    Item3.5    R_30f124c2-f784-43ca-ad2d-4158c04b2f77    45
1    Item3    Hot Text    Item3.6    R_7326104d-c143-4826-b5ce-bfa9e5cc589f    46
1    Item3    InLine Image    Item3.7    R_b40b1ea8-c47b-469a-af07-4071eb60e838    47
1    Item3    InLine Instructor Text    Item3.8    R_67e29489-283b-41af-b9e8-eed6eb036977    48
1    Item3    InLine Quote    Item3.9    R_8b7046d5-f2d9-4f20-bdaf-e27a42cdd7b5    49
1    Item3    InLine Term    Item3.10    R_5dc51146-2867-43ce-a09b-8788f976e52d    50
1    Item3    Internal Cross Ref    Item3.11    R_6c2fda92-2edc-47b1-9481-f3d7ab6e47b0    51
1    Item3    Internal Link    Item3.12    R_aab7704d-0783-4ea1-b70a-b0208efe0d29    52
1    Item3    URL    Item3.13    R_b8ecf46d-d7b2-4ef9-91f1-82f079f83fb3    53
1    Item3    Variable Text    Item3.14    R_5fb75249-8a7d-49af-beba-ba32a7c16312    54
1    Item3    Not Tested    Item3.15    R_b8cbcf89-82d0-4087-a502-2446d847cdb4    55
1    Item3    Citation    Item3.2    R_5c1ed0d6-a8e1-438a-8a9f-a6356f1ad659    56
1    Item3    Equation    Item3.3    R_041ceda3-7404-4362-ac7b-b5abbf50b43b    57
0    NULL    Question Block Features    Item4    NULL    58
1    Item4    Sequencing Activity Content    FolderOfItem4.10    NULL    59
1    Item4    Fill-in Activity    Item4.4    R_7f343db0-65e2-42c6-8bb9-709267aa19f1    60
1    Item4    Image Map Assessment    Item4.5    R_2af9aef0-b680-4c9f-8ced-c5fded5eab53    61
2    FolderOfItem4.10    Sequencing Activity Content    Item4.10    R_18855d19-e7f4-4317-8fba-8982dc6c8ec1    62
2    FolderOfItem4.10    Select Sequence Number    Item4.10.2    R_b3b0a1b2-6713-4429-a99d-36c919902d98    63
1    Item4    Matching Activity Content    Item4.6    R_a7cb91b7-a004-44ca-b363-fe086e86beee    64
1    Item4    Select Hot Text    Item4.9    R_a80aa9c8-df70-41c6-afd4-4c8a1dbf1f8e    65
2    FolderOfItem4.10    Sequence via Drag and Drop    Item4.10.3    R_f673ae61-35ac-483e-a3c6-38a6ab970230    66
1    Item4    Not Currently Supported in the Xyleme Web Publisher    FolderOfItem4.12    NULL    67
2    FolderOfItem4.12    Not Currently Supported in the Xyleme Web Publisher    Item4.12    R_86a0d2cc-1097-47d9-9dc2-ea578c4db6d2    68
2    FolderOfItem4.12    Branching Simulation    Item4.12.2    R_ebc373e0-170a-492c-9f76-159cdd3fbffe    69
2    FolderOfItem4.12    Compound Question    Item4.12.3    R_b61168b9-90b2-43fd-b566-45b746ad9b98    70
2    FolderOfItem4.12    Ledger    Item4.12.4    R_8654df48-9d5e-4b80-98c6-84f911775a60    71
1    Item4    Matrix    FolderOfItem4.7    NULL    72
2    FolderOfItem4.7    Matrix    Item4.7    R_46e0b0d5-bcc3-4df8-b2c6-d9c791773d24    73
2    FolderOfItem4.7    Matrix with Radio Buttons    Item4.7.2    R_6b8302cc-10fc-4fd4-bef3-99514b5d1864    74
2    FolderOfItem4.7    Matrix with Checkboxes    Item4.7.3    R_b4698485-b538-4839-8329-fc14239604e0    75
1    Item4    Multiple Choice Content    FolderOfItem4.8    NULL    76
2    FolderOfItem4.8    Multiple Choice Content    Item4.8    R_877eb76b-e5d1-432f-912a-2ef7e82492df    77
2    FolderOfItem4.8    Multiple Choice (One Correct Answer)    Item4.8.2    R_0a19e2fe-9603-41de-bd1e-17c1eb018bd2    78
2    FolderOfItem4.8    Multiple Select (Multiple Correct Answers)    Item4.8.3    R_ed38bbfd-bbde-48cc-b29c-b3391ba0ae68    79
1    Item4    Bin Drop    Item4.1    R_6a7fd3e1-ecfe-4ea3-a18d-27e24ed4cc2a    80
1    Item4    Video Assessment    Item4.11    R_698040a8-0090-48a8-922f-50ec38dcad67    81
1    Item4    Not Tested    Item4.13    R_fc0410a7-5cb9-4480-a9bd-06faa0f7eae1    82
1    Item4    Drag and Drop    Item4.2    R_e50dfe37-f6c3-4723-98b2-6832d7af0732    83
1    Item4    Essay Activity    Item4.3    R_e70732a3-4d28-4808-b1dd-3360ab3797da    84

#11

2.20, 2.21, 2.22 is coming before 2.3....

complete xml is mentioned below:

<manifest xmlns="http://www.imsglobal.org/xsd/imscp_v1p1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:adlcp="http://www.adlnet.org/xsd/adlcp_v1p3" xmlns:adlseq="http://www.adlnet.org/xsd/adlseq_v1p3" xmlns:adlnav="http://www.adlnet.org/xsd/adlnav_v1p3" xmlns:imsss="http://www.imsglobal.org/xsd/imsss" xsi:schemaLocation="http://www.imsglobal.org/xsd/imscp_v1p1              imscp_v1p1.xsd    http://www.adlnet.org/xsd/adlcp_v1p3 adlcp_v1p3.xsd              http://www.adlnet.org/xsd/adlseq_v1p3    adlseq_v1p3.xsd              http://www.adlnet.org/xsd/adlnav_v1p3 adlnav_v1p3.xsd    http://www.imsglobal.org/xsd/imsss imsss_v1p0.xsd" identifier="SSc7f9dfdf-fb80-47ed-aa5e-2fe669acb4fd"><metadata><CourseCode>8000</CourseCode><JulianDate>07-06-15</JulianDate><schema>ADL SCORM</schema><schemaversion>2004 3rd Edition</schemaversion><lms>Default</lms><adlcp:location>metadata_course.xml</adlcp:location></metadata><organizations default="defaultOrganization"><organization identifier="defaultOrganization"><title>eBooks Features Sampler Course</title><item identifier="Item1" isvisible="true"><title>Introduction</title><item identifier="Item1.1" identifierref="R_8fcc4b01-6743-4ca3-b681-fcd518b84a07" isvisible="true"><title>Cover Page</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0"><imsss:rollupRule childActivitySet="all"><imsss:rollupConditions><imsss:rollupCondition condition="completed" /></imsss:rollupConditions><imsss:rollupAction action="satisfied" /></imsss:rollupRule></imsss:rollupRules></imsss:sequencing></item><item identifier="Item2" isvisible="true"><title>Parablock Features</title><item identifier="Item2.1" identifierref="R_776215d7-5a21-41b4-9722-7b35127ea40a" isvisible="true"><title>Audio</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.2" identifierref="R_638ea958-c830-4360-8220-149ef03842ea" isvisible="true"><title>Bounding Rectangle</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.3" identifierref="R_00b86664-0f14-4704-ae51-cf1d738af877" isvisible="true"><title>Categories</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.4" identifierref="R_a3fb3b0f-e110-4b35-a7f6-a93c0e480dd8" isvisible="true"><title>Custom Note</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.5" identifierref="R_ef3f793e-2e36-452b-9727-473a30d4533e" isvisible="true"><title>Doc Writer</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.6" identifierref="R_a9e0e5af-390e-4481-b801-386fcb0d8bdf" isvisible="true"><title>Equation</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="FolderOfItem2.7" isvisible="true"><title>Figures</title><item identifier="Item2.7" identifierref="R_5bbdc574-6795-429e-9850-8d4cdd3c7a98" isvisible="true"><title>Figures</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.7.2" identifierref="R_ec3c782c-6a73-4889-827a-0098a4580a95" isvisible="true"><title>In the Content Area</title><imsss:sequencing /></item><item identifier="Item2.7.3" identifierref="R_538cd6df-a659-45d8-8272-707a66ca107e" isvisible="true"><title>In the Banner</title><imsss:sequencing /></item><item identifier="Item2.7.4" identifierref="R_454bbb5e-b32d-4e68-8f36-2e01dcabfffe" isvisible="true"><title>Diagram</title><imsss:sequencing /></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="FolderOfItem2.8" isvisible="true"><title>Filter Metadata</title><item identifier="Item2.8" identifierref="R_2347a6c1-cb16-4275-a2ef-f640d9ec069a" isvisible="true"><title>Filter Metadata</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.8.2" identifierref="R_22b1dd74-255d-4b86-95a7-6faa01d0ff1f" isvisible="true"><title>Topic Filtered for Instructor</title><imsss:sequencing /></item><item identifier="Item2.8.3" identifierref="R_882c0ec0-c367-4429-a480-5f6c29e89fe5" isvisible="true"><title>Topic Filtered for Student</title><imsss:sequencing /></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.9" identifierref="R_2c167a85-3d25-4307-9697-c055b643241f" isvisible="true"><title>Flash Cards</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.10" identifierref="R_513f7866-6879-45eb-98e3-d2babfcf22dd" isvisible="true"><title>Glossary Item</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.11" identifierref="R_aaaf2f0c-a5a6-4968-807c-2498276e74dd" isvisible="true"><title>Icon</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.12" identifierref="R_a9c10dc6-6b14-4c1a-b61d-c23ec2b73b57" isvisible="true"><title>Image Map</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="FolderOfItem2.13" isvisible="true"><title>List</title><item identifier="Item2.13" identifierref="R_630f40b2-1937-4b66-8fed-bdb2deda3138" isvisible="true"><title>List</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.13.2" identifierref="R_58f80740-ccfc-41f4-ab9f-1438143d301c" isvisible="true"><title>List Marker Time!</title><imsss:sequencing /></item><item identifier="Item2.13.3" identifierref="R_781e9c18-5e81-4274-9894-78746762771f" isvisible="true"><title>New Bullet Styles</title><imsss:sequencing /></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.14" identifierref="R_a21f68ea-5683-4a0d-b01b-83e3cf712c70" isvisible="true"><title>Margin Note</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="FolderOfItem2.15" isvisible="true"><title>Movie Object</title><item identifier="Item2.15" identifierref="R_5e5394f1-d002-432c-b633-e98d81d8f495" isvisible="true"><title>Movie Object</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.15.2" identifierref="R_5969d8e3-1924-4dd8-b7c9-c3b2773ea439" isvisible="true"><title>Movie Object with Embed Code</title><imsss:sequencing /></item><item identifier="Item2.15.3" identifierref="R_47e3b4a9-51fb-4469-a5d0-f56b18f9d4b2" isvisible="true"><title>Movie Object with Local File</title><imsss:sequencing /></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.16" identifierref="R_2c12998c-9c30-4996-856f-4d0f1ab07ff5" isvisible="true"><title>Quote Block</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.17" identifierref="R_27aff339-7a58-4606-87f2-5b0971a91f37" isvisible="true"><title>Table</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="FolderOfItem2.18" isvisible="true"><title>Tabs</title><item identifier="Item2.18" identifierref="R_8b865f3d-1555-4ffc-a056-015b4e2a72f8" isvisible="true"><title>Tabs</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.18.2" identifierref="R_c4b22bb1-4e31-470c-875b-0f70da326a6c" isvisible="true"><title>Vertical Tabs</title><imsss:sequencing /></item><item identifier="Item2.18.3" identifierref="R_ef6b959f-6f84-457d-a782-8d8489b9d4cb" isvisible="true"><title>Horizontal Tabs</title><imsss:sequencing /></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.19" identifierref="R_d6893207-3d5a-4ba1-903f-efbf7f677a76" isvisible="true"><title>Titled Block</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.20" identifierref="R_f36ad2c0-0dc0-46a1-9d75-389eede9707d" isvisible="true"><title>XML Slide</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item2.21" identifierref="R_de8215ee-2ff9-4918-9d8b-03bab00092e3" isvisible="true"><title>Not Tested</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0"><imsss:rollupRule childActivitySet="all"><imsss:rollupConditions><imsss:rollupCondition condition="completed" /></imsss:rollupConditions><imsss:rollupAction action="satisfied" /></imsss:rollupRule></imsss:rollupRules></imsss:sequencing></item><item identifier="Item3" isvisible="true"><title>InLine Features</title><item identifier="Item3.1" identifierref="R_199cc06b-8ac8-4608-942a-1489c74a64ac" isvisible="true"><title>Autonumber</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.2" identifierref="R_5c1ed0d6-a8e1-438a-8a9f-a6356f1ad659" isvisible="true"><title>Citation</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.3" identifierref="R_041ceda3-7404-4362-ac7b-b5abbf50b43b" isvisible="true"><title>Equation</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.4" identifierref="R_8e2e4382-7df0-472a-86f6-a93504b45714" isvisible="true"><title>Filter Metadata</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.5" identifierref="R_30f124c2-f784-43ca-ad2d-4158c04b2f77" isvisible="true"><title>Footnote</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.6" identifierref="R_7326104d-c143-4826-b5ce-bfa9e5cc589f" isvisible="true"><title>Hot Text</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.7" identifierref="R_b40b1ea8-c47b-469a-af07-4071eb60e838" isvisible="true"><title>InLine Image</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.8" identifierref="R_67e29489-283b-41af-b9e8-eed6eb036977" isvisible="true"><title>InLine Instructor Text</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.9" identifierref="R_8b7046d5-f2d9-4f20-bdaf-e27a42cdd7b5" isvisible="true"><title>InLine Quote</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.10" identifierref="R_5dc51146-2867-43ce-a09b-8788f976e52d" isvisible="true"><title>InLine Term</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.11" identifierref="R_6c2fda92-2edc-47b1-9481-f3d7ab6e47b0" isvisible="true"><title>Internal Cross Ref</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.12" identifierref="R_aab7704d-0783-4ea1-b70a-b0208efe0d29" isvisible="true"><title>Internal Link</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.13" identifierref="R_b8ecf46d-d7b2-4ef9-91f1-82f079f83fb3" isvisible="true"><title>URL</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.14" identifierref="R_5fb75249-8a7d-49af-beba-ba32a7c16312" isvisible="true"><title>Variable Text</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item3.15" identifierref="R_b8cbcf89-82d0-4087-a502-2446d847cdb4" isvisible="true"><title>Not Tested</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0"><imsss:rollupRule childActivitySet="all"><imsss:rollupConditions><imsss:rollupCondition condition="completed" /></imsss:rollupConditions><imsss:rollupAction action="satisfied" /></imsss:rollupRule></imsss:rollupRules></imsss:sequencing></item><item identifier="Item4" isvisible="true"><title>Question Block Features</title><item identifier="Item4.1" identifierref="R_6a7fd3e1-ecfe-4ea3-a18d-27e24ed4cc2a" isvisible="true"><title>Bin Drop</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.2" identifierref="R_e50dfe37-f6c3-4723-98b2-6832d7af0732" isvisible="true"><title>Drag and Drop</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.3" identifierref="R_e70732a3-4d28-4808-b1dd-3360ab3797da" isvisible="true"><title>Essay Activity</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.4" identifierref="R_7f343db0-65e2-42c6-8bb9-709267aa19f1" isvisible="true"><title>Fill-in Activity</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.5" identifierref="R_2af9aef0-b680-4c9f-8ced-c5fded5eab53" isvisible="true"><title>Image Map Assessment</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.6" identifierref="R_a7cb91b7-a004-44ca-b363-fe086e86beee" isvisible="true"><title>Matching Activity Content</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="FolderOfItem4.7" isvisible="true"><title>Matrix</title><item identifier="Item4.7" identifierref="R_46e0b0d5-bcc3-4df8-b2c6-d9c791773d24" isvisible="true"><title>Matrix</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item4.7.2" identifierref="R_6b8302cc-10fc-4fd4-bef3-99514b5d1864" isvisible="true"><title>Matrix with Radio Buttons</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.7.3" identifierref="R_b4698485-b538-4839-8329-fc14239604e0" isvisible="true"><title>Matrix with Checkboxes</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>0</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="FolderOfItem4.8" isvisible="true"><title>Multiple Choice Content</title><item identifier="Item4.8" identifierref="R_877eb76b-e5d1-432f-912a-2ef7e82492df" isvisible="true"><title>Multiple Choice Content</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item4.8.2" identifierref="R_0a19e2fe-9603-41de-bd1e-17c1eb018bd2" isvisible="true"><title>Multiple Choice (One Correct Answer)</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.8.3" identifierref="R_ed38bbfd-bbde-48cc-b29c-b3391ba0ae68" isvisible="true"><title>Multiple Select (Multiple Correct Answers)</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>0</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.9" identifierref="R_a80aa9c8-df70-41c6-afd4-4c8a1dbf1f8e" isvisible="true"><title>Select Hot Text</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="FolderOfItem4.10" isvisible="true"><title>Sequencing Activity Content</title><item identifier="Item4.10" identifierref="R_18855d19-e7f4-4317-8fba-8982dc6c8ec1" isvisible="true"><title>Sequencing Activity Content</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item4.10.2" identifierref="R_b3b0a1b2-6713-4429-a99d-36c919902d98" isvisible="true"><title>Select Sequence Number</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.10.3" identifierref="R_f673ae61-35ac-483e-a3c6-38a6ab970230" isvisible="true"><title>Sequence via Drag and Drop</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>0</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.11" identifierref="R_698040a8-0090-48a8-922f-50ec38dcad67" isvisible="true"><title>Video Assessment</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="FolderOfItem4.12" isvisible="true"><title>Not Currently Supported in the Xyleme Web Publisher</title><item identifier="Item4.12" identifierref="R_86a0d2cc-1097-47d9-9dc2-ea578c4db6d2" isvisible="true"><title>Not Currently Supported in the Xyleme Web Publisher</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><item identifier="Item4.12.2" identifierref="R_ebc373e0-170a-492c-9f76-159cdd3fbffe" isvisible="true"><title>Branching Simulation</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.12.3" identifierref="R_b61168b9-90b2-43fd-b566-45b746ad9b98" isvisible="true"><title>Compound Question</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.12.4" identifierref="R_8654df48-9d5e-4b80-98c6-84f911775a60" isvisible="true"><title>Ledger</title><imsss:sequencing><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0" /><imsss:objectives><imsss:primaryObjective satisfiedByMeasure="true"><imsss:minNormalizedMeasure>0</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></item><item identifier="Item4.13" identifierref="R_fc0410a7-5cb9-4480-a9bd-06faa0f7eae1" isvisible="true"><title>Not Tested</title><imsss:sequencing><imsss:rollupRules objectiveMeasureWeight="0" /></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:rollupRules objectiveMeasureWeight="0"><imsss:rollupRule childActivitySet="all"><imsss:rollupConditions><imsss:rollupCondition condition="completed" /></imsss:rollupConditions><imsss:rollupAction action="satisfied" /></imsss:rollupRule></imsss:rollupRules></imsss:sequencing></item><imsss:sequencing><imsss:controlMode choiceExit="true" useCurrentAttemptObjectiveInfo="false" useCurrentAttemptProgressInfo="false" flow="true" choice="true" forwardOnly="false" /><imsss:auxiliaryResources><imsss:auxiliaryResource auxiliaryResourceID="c7f9dfdf-fb80-47ed-aa5e-2fe669acb4fd.html" purpose="Glossary" /></imsss:auxiliaryResources><imsss:objectives><imsss:primaryObjective objectiveID="SSPObjective" satisfiedByMeasure="true"><imsss:minNormalizedMeasure>1</imsss:minNormalizedMeasure></imsss:primaryObjective></imsss:objectives></imsss:sequencing></organization></organizations></manifest>

#12

Does it get you the right numbering if you use

ROW_NUMBER() OVER (ORDER BY [Identifier]) AS SN

instead of

ROW_NUMBER() OVER (ORDER BY N) AS SN

If it does, it has to do with my misunderstanding of how the nodes are to be ordered.


#13

ROW_NUMBER() OVER (ORDER BY Identifier) is not at all working.

Initially i thought ROW_NUMBER() OVER (ORDER BY N) AS SN resolved the issue but when i checked it with full xml, it still is giving issues in few cases as i explained before:
2.20, 2.21, 2.22 is coming before 2.3....
1.19 is coming before 1.2

Please try and check your solution with full xml. It is working in case float values have just one digit after dot (like 2.1,3.2,5.7). but it is failing in case there are two digits after dot like 1.20,2.21.

Hope that clarifies the error cases.
thanks again.


#14

I am afraid I am confused now. The query was set up so that 2.20, 2.21 etc. will be sorted before 2.3. That is because in string sort, 2.20 and 2.21 are lower than 2.3. (and even in numeric sort). So now I think I don't understand the requirement at all.


#15

Sorry if i have confused you.

To clarify a bit, result should show like below and then numbering will work correctly:
identifier
1.1
1.2
1.3
1.4
2.1
2.2
2.2.1
2.2.2
2.2.3
2.2.4
2.3
2.4
2.5
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
3.10
3.10.1
3.10.2
3.11
3.12
4.1
4.2

Hope I am able to clarify the requirement


#16

Neither string sort, nor numeric sort would work for that case. Unless you can do something while shredding the XML the only other way I can think of is to split each of the parts (eg 3.10.2 into 3, 10, and 2) and then order numerically using the 3 (or however many parts there are).


#17

Yes. I will try to do this way by breaking it into parts in query and then do a sort


#18

Just to close the ticket - I was able to resolve the issue by breaking string into parts on the basis of dot ( with the help of UDF in cte query).

Thanks everyone for your wfforts


#19

Hi

There is a way to randomly autoincrement using this
ROW_NUMBER() OVER (ORDER BY (select 1))
Please note the (select 1) .. this is the key

;
WITH XMLNAMESPACES (DEFAULT N'http://www.imsglobal.org/xsd/imscp_v1p1')
	,Hierarchy (
	[Counter]
	,[Level]
	,[ParentName]
	,[Title]
	,[Identifier]
	,[PageReference]
	,[Children]
	,N
	)
AS (
	SELECT ROW_NUMBER() OVER (ORDER BY [Par].value('(@identifier[1])', 'varchar(100)')),0 AS [Level]
		,CONVERT(VARCHAR(100), NULL) AS [ParentName]
		,[Par].value('(title/text())[1]', 'varchar(500)') AS [Title]
		,[Par].value('(@identifier[1])', 'varchar(100)') AS [Identifier]
		,[Par].value('(@identifierref[1])', 'varchar(200)') AS [PageReference]
		,[Par].query('./*') AS [Children]
		,CAST(ROW_NUMBER() OVER (ORDER BY [Par].value('(@identifier[1])', 'varchar(100)')) AS VARCHAR(32)) AS N
	FROM @xml.nodes('manifest/organizations/organization/item') Parents([Par])
	
	UNION ALL
	
	SELECT ROW_NUMBER() OVER (ORDER BY [Level])
		,LEVEL + 1 AS [Level]
		,h.[Identifier] AS [ParentName]
		,[NL].value('(title/text())[1]', 'varchar(500)') AS [Title]
		,[NL].value('(@identifier[1])', 'varchar(100)') AS [Identifier]
		,[NL].value('(@identifierref[1])', 'varchar(200)') AS [PageReference]
		,[NL].query('./*') AS [Children]
		,CAST(N + CAST(ROW_NUMBER() OVER (ORDER BY [NL].value('(@identifier[1])', 'varchar(100)')) AS VARCHAR(32)) AS VARCHAR(32)) AS N
	FROM Hierarchy h
	CROSS APPLY h.Children.nodes('./item') NextLevel([NL])
	)
SELECT [Counter]
	,[Level]
	,[ParentName]
	,[Title]
	,[Identifier]
	,[PageReference]
	,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SN
FROM Hierarchy
ORDER BY ROW_NUMBER() OVER (ORDER BY N);