选择 XML 节点作为行 [英] Select XML nodes as rows

问题描述

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

I am selecting from a table that has an XML column using T-SQL. I would like to select a certain type of node and have a row created for each one.

例如，假设我从 people 表中进行选择.该表有一个用于地址的 XML 列.XML 的格式类似于以下内容:

For instance, suppose I am selecting from a people table. This table has an XML column for addresses. The XML is formated similar to the following:

<address>
  <street>Street 1</street>
  <city>City 1</city>
  <state>State 1</state>
  <zipcode>Zip Code 1</zipcode>
</address>
<address>
  <street>Street 2</street>
  <city>City 2</city>
  <state>State 2</state>
  <zipcode>Zip Code 2</zipcode>
</address>

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

How can I get results like this:

名称      城市    ;  状态

Name         City         State

乔贝克   西雅图    华盛顿

Joe Baker   Seattle      WA

Joe Baker  塔科马    WA

Joe Baker   Tacoma     WA

Fred Jones  温哥华 BC

Fred Jones  Vancouver BC

推荐答案

这是您的解决方案:

/* TEST TABLE */
DECLARE @PEOPLE AS TABLE ([Name] VARCHAR(20),  [Address] XML )
INSERT INTO @PEOPLE SELECT 
    'Joel', 
    '<address>
      <street>Street 1</street>
      <city>City 1</city>
      <state>State 1</state>
      <zipcode>Zip Code 1</zipcode>
    </address>
    <address>
      <street>Street 2</street>
      <city>City 2</city>
      <state>State 2</state>
      <zipcode>Zip Code 2</zipcode>
    </address>'
UNION ALL SELECT
    'Kim', 
    '<address>
      <street>Street 3</street>
      <city>City 3</city>
      <state>State 3</state>
      <zipcode>Zip Code 3</zipcode>
    </address>'

SELECT * FROM @PEOPLE

-- BUILD XML
DECLARE @x XML
SELECT @x = 
( SELECT 
      [Name]
    , [Address].query('
            for $a in //address
            return <address 
                street="{$a/street}" 
                city="{$a/city}" 
                state="{$a/state}" 
                zipcode="{$a/zipcode}" 
            />
        ') 
  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

这篇关于选择 XML 节点作为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！