How can I iterate through all nodes in XML - Sql2008

Question

How can I iterate through all nodes in XML document using T-SQL (SQL2008). I need to create table schemas for each distinct Node Path (elements) in the document.

declare @x xml='
<logins>
  <roles>
    <role name="Administrator" />
    <role name="elUser" />
    <role name="ElAdministrator"/>
    <role name="regionalManager" />
    <role name="Rep"/>
    <role name="DiscountAdministrator" />
    <role name="LoginAdmin"/>
    <groups>
      <group name="Administrators">
        <role name="Administrator"/>
        <role name="elUser" />
        <role name="ElAdministrator" />
        <role name="Rep" />
        <role name="regionalManager" />
        <role name="DiscountAdministrator" />
        <role name="LoginAdmin" />
      </group>
    </groups>
  </roles>
  <members>
    <member login="apeiris" ofgroup="Administrator"></member>
  </members>
</logins>'

Edited to include above, I am thinking of generic script to create /maintain schema, means that I do not know nodes before hand.

Have a look at [How can I get a list of element names from XML](http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server). The question is otherwise unclear on what you are trying to achieve — RichardTheKiwi, Mar 14 '11 at 21:01
Don't you need to know at some point what you are going to do with the data you extrapolate? Example you are going to have to know that you are adding roles if the path is /logins/roles/role... — DiningPhilanderer, Mar 14 '11 at 21:15

score 1 · Answer 1 · answered Feb 18 '12 at 05:42

The following code can be used to map the whole xml structure and values stored on a document from which we only know the name of its root.

When I tested it it worked for just a few runs, afterwards it kind of sucked too much of the sql resources and unrelated transactions started to suffer.

I tried looking for ways to make it work with the nodes() function but only the old OPENXML did what I needed done.

I then inserted it on a table variables and manipulated it.

For this specific question, Dynamic sql could be generated to verify if the necessary tables exists or not and after creating them if needed the proceed with value insertion.

DECLARE @idoc INT
DECLARE @param_XML XML = 
'<root>
    <element1>hello
        <element2>1</element2>
        <element3 id="3">goodbye</element3>
    </element1>
</root>'

EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @param_XML


--SELECT id, parentid, nodetype,localname, prev, [text]
SELECT *
FROM OPENXML (@idoc, '/root', 2)

-- remeber to close document to avoid memory leak
EXEC sys.sp_xml_removedocument @idoc

score 0 · Answer 2 · answered Oct 25 '13 at 19:03

If the structure remains like you posted so you don't need to worry if more roles are added

try this:

declare @x xml='
<logins>
<roles>
<role name="Administrator" />
<role name="elUser" />
<role name="ElAdministrator"/>
<role name="regionalManager" />
<role name="Rep"/>
<role name="DiscountAdministrator" />
<role name="LoginAdmin"/>
<groups>
  <group name="Administrators">
    <role name="Administrator"/>
    <role name="elUser" />
    <role name="ElAdministrator" />
    <role name="Rep" />
    <role name="regionalManager" />
    <role name="DiscountAdministrator" />
    <role name="LoginAdmin" />
  </group>
</groups>
    <groups>
  <group name="SimpleUser">
    <role name="Administrator"/>
    <role name="elUser" />
    <role name="ElAdministrator" />
    <role name="Rep" />
    <role name="regionalManager" />
    <role name="DiscountAdministrator" />
    <role name="LoginAdmin" />
  </group>
</groups>
</roles>
<members>
<member login="apeiris" ofgroup="Administrator"></member>
</members>
</logins>'

select 
row_number() over (partition by 
t.x.value('(group/@name)[1]' , 'varchar(100)') 
order by t.x.value('(group/@name)[1]' , 'varchar(100)')) Id,
t.x.value('(group/@name)[1]' , 'varchar(100)') GroupName,
p.y.value('@name' , 'varchar(100)') RoleName
from @x.nodes('//logins/roles/role') p(y)
cross apply @x.nodes('//groups') t(x)

It provides all roles and groups in the xml

DiningPhilanderer · Answer 3 · 2011-03-14T19:36:29.680

-1

I am pretty sure if you know what nodes are going to be tables you can use an XPath statment... Do you have a sample of the file?

This gets me all 7 of your rolenames:

SELECT      Logins.L.query('data(@name)').value('.', 'varchar(60)') as RoleName
FROM        @x.nodes('/logins/roles/role') Logins (L)

From here you can use the same idea to get the data you need and execute any SQL based off the SELECT results however you choose.

edited Mar 14 '11 at 19:36

answered Mar 14 '11 at 19:09

DiningPhilanderer

2,737
3
25
29

Guilty as charged, I did not notice the qualifier added to the question at the very end... – DiningPhilanderer Mar 16 '11 at 18:33

How can I iterate through all nodes in XML - Sql2008

3 Answers3