How can I iterate through all nodes in XML - Sql2008

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>
    <memb开发者_开发知识库er 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.

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

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

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.