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
