asked on

QUERY REFACTOR TO FIND NODE ORDER

Please help - I need to refactor the query to order the result set based on the order of the values in the xml file its reading.
for example if RequestId was the first xml node then I would want that to return first.

The way I have this query formatted it is returning alphabettically.

SELECT
n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #AR2
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'div',
	'p',
	'strong',
	'u',
	'html', 
	'RequestHeader')
	

   select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from #AR2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

   set @query = 
'SELECT ' + @cols + ' INTO AR from 
             (
              select Name, Value
			  from #AR2
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p ORDER BY RequestID'

   execute(@query);

Open in new window

Thanks

Ryan Chong

how's your XML looks like?

Robb Hill

ASKER

The xml can be any level of nodes//parents ..childs etc...this needs to be structured so it doesnt care what the xml looks like.

this query will give me every column..just no order.

      SELECT
            n.value('local-name(.)','NVARCHAR(MAX)') AS Name,
            n.value('text()[1]','NVARCHAR(MAX)') AS Value
            INTO #AR2
            FROM @ProjectXML.nodes('//*') AS ProjectXML(n)

Robb Hill

ASKER

I honestly do not think this is an xml question. As you can see in the original question I run a pivot that inverts rows to columns.

When it does this the order of the columns is in alphabeticaly. Is there a way to order by the order of the xml elements.

The first query does not order it alphabettically..its not until I pivot that it does it...making me think its something with this pivot logic I do not understand.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robb Hill

ASKER

Thanks that did the trick!