sql - 在事先不知道模式的情况下解析 XML 的更好方法

Question

使用 SQL Server 2005，我需要在事先不知道元素名称的情况下解析 XML 中的所有值。

这是我的:

declare @xml xml

--REMEMBER THIS XML COULD CONTAIN ANYTHING. THIS IS AN EXAMPLE
set @xml = '
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>1</id>
  <name>asdf</name>
  <siteaddress1>asdf</siteaddress1>
  <siteaddress2>asdf</siteaddress2>
  <sitecity>asdf</sitecity>
  <sitestateid>4</sitestateid>
  <sitezip>333333</sitezip>
  <phone>asdfsadf</phone>
  <epaid>34343</epaid>
</row>'

declare @rows int
declare @i int
declare @sql nvarchar(max)

set @i = 0
set @rows = (SELECT Tbl.Col.value('count(*)', 'int') FROM @xml.nodes('//row') Tbl(Col))
set @sql = ''

while (@i <= @rows)
begin
    set @i = @i + 1
    set @sql = @sql + 'select Tbl.Col.value(''*[' + cast(@i as varchar(10)) + ']'', ''varchar(max)'') 
                       FROM @x.nodes(''//row'') Tbl(Col)
                       union '
end

set @sql = substring(@sql,0,len(@sql)-5)

exec sp_executesql @sql,N'@x xml', @x = @xml;

这行得通，但它很丑而且不是基于集合的。必须有更好的方法。

编辑。在我的例子中，架构将始终是

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
...
...
...
</row>

Answer 1

给你:

DECLARE @xml XML

set @xml = '
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>1</id>
  <name>asdf</name>
  <siteaddress1>asdf</siteaddress1>
  <siteaddress2>asdf</siteaddress2>
  <sitecity>asdf</sitecity>
  <sitestateid>4</sitestateid>
  <sitezip>333333</sitezip>
  <phone>asdfsadf</phone>
  <epaid>34343</epaid>
</row>'

select r.value('fn:local-name(.)', 'nvarchar(50)') as 'elementName',
    r.value('.','nvarchar(max)') AS 'value'
FROM
    @xml.nodes('//row/*') AS records(r)

如果您只想要不同的元素名称，您只需要做:

select distinct r.value('fn:local-name(.)', 'nvarchar(50)') as t FROM
    @xml.nodes('//row/*') AS records(r)