Как я могу перебрать все узлы в XML - Sql2008

Следующий код можно использовать для сопоставления всей структуры xml и значений, хранящихся в документе, из которого мы знаем только имя его корня.

Когда я тестировал его, он работал всего несколько раз, после чего он высасывал слишком много ресурсов sql, и несвязанные транзакции начали страдать.

Я пытался найти способы заставить его работать с функцией nodes(), но только старый OPENXML делал то, что мне было нужно.

Затем я вставил его в переменные таблицы и манипулировал им.

Для этого конкретного вопроса может быть сгенерирован динамический sql, чтобы проверить, существуют ли необходимые таблицы или нет, и после их создания, если необходимо, продолжить вставку значений.

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

Если структура остается такой же, как вы опубликовали, вам не нужно беспокоиться о добавлении дополнительных ролей.

попробуй это:

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)

Он предоставляет все роли и группы в xml

Я почти уверен, что если вы знаете, какие узлы будут таблицами, вы можете использовать статус XPath... У вас есть образец файла?

Это дает мне все 7 ваших ролевых имен:

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

Отсюда вы можете использовать ту же идею, чтобы получить необходимые данные и выполнить любой SQL на основе результатов SELECT, как бы вы ни выбрали.