选择XML节点作为行

我从一个使用T-SQL的XML列中选择一个表.我想选择某种类型的节点,并为每个节点创建一行.

例如,假设我从人员表中进行选择.该表具有地址的XML列.XML的格式类似于以下内容:

  Street 1
  City 1
  State 1
  Zip Code 1


  Street 2
  City 2
  State 2
  Zip Code 2

我怎样才能得到这样的结果:

姓         城市         州

Joe Baker西雅图华盛顿

Joe Baker Tacoma WA

弗雷德琼斯温哥华BC

这是你的解决方案:

/* TEST TABLE */
DECLARE @PEOPLE AS TABLE ([Name] VARCHAR(20),  [Address] XML )
INSERT INTO @PEOPLE SELECT 
    'Joel', 
    '

      Street 1
      City 1
      State 1
      Zip Code 1
    
    

      Street 2
      City 2
      State 2
      Zip Code 2
    
'
UNION ALL SELECT
    'Kim', 
    '

      Street 3
      City 3
      State 3
      Zip Code 3
    
'

SELECT * FROM @PEOPLE

-- BUILD XML
DECLARE @x XML
SELECT @x = 
( SELECT 
      [Name]
    , [Address].query('
            for $a in //address
            return 

        ') 
  FROM @PEOPLE AS people 
  FOR XML AUTO
) 

-- RESULTS
SELECT [Name]    = T.Item.value('../@Name', 'varchar(20)'),
       street    = T.Item.value('@street' , 'varchar(20)'),
       city      = T.Item.value('@city'   , 'varchar(20)'),
       state     = T.Item.value('@state'  , 'varchar(20)'),
       zipcode   = T.Item.value('@zipcode', 'varchar(20)')
FROM   @x.nodes('//people/address') AS T(Item)

/* OUTPUT*/

Name | street   | city   | state   | zipcode
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Joel | Street 1 | City 1 | State 1 | Zip Code 1
Joel | Street 2 | City 2 | State 2 | Zip Code 2
Kim  | Street 3 | City 3 | State 3 | Zip Code 3