将一个XML文本显示成行

------------------------------------------------------------------

DECLARE @doc xml;
SET @doc='<biglottos> <N1>1 </N1> <N2>2 </N2> <N3>4 </N3> </biglottos>'

SELECT
    A.x.query('N1').value('.','int')N1  ,
    A.x.query('N2').value('.','int')N2  ,
    A.x.query('N3').value('.','int')N3 
FROM @doc.nodes('/biglottos') AS A(x)

/*
N1          N2          N3
----------- ----------- -----------
1           2           4

(1 行受影响)

*/

declare @xml xml
set @xml='
<Person>
  <Node>
    <Name>admin</Name>
    <Date>2010-4-6</Date>
  </Node>
  <Node>
    <Name>lisi</Name>
    <Date>2010-4-6</Date>
  </Node>
  <Node>
    <Name>wangwu</Name>
    <Date>2010-4-7</Date>
  </Node>
</Person>'

SELECT
    A.x.query('Name').value('.','varchar(10)') Name  ,
    A.x.query('Date').value('.','datetime') Date
FROM @xml.nodes('//Node') AS A(x)

SELECT

    x.value('Name[1]','varchar(10)') Name  ,

    x.value('Date[1]','datetime') Date

FROM @xml.nodes('//Node') AS A(x)

/*

Name       Date

---------- -----------------------

admin      2010-04-06 00:00:00.000

lisi       2010-04-06 00:00:00.000

wangwu     2010-04-07 00:00:00.000

 

(3 行受影响)

*/

 

declare @xml xml
set @xml='
<Person>
  <Node>
    <Name>admin</Name>
    <Date>2010-4-6</Date>
  </Node>
  <Node>
    <Name>lisi</Name>
    <Date>2010-4-6</Date>
  </Node>
  <Node>
    <Name>wangwu</Name>
    <Date>2010-4-7</Date>
  </Node>
</Person>'

SELECT
    A.x.value('(Node/Name)[1]','varchar(10)') Name1, 
    A.x.value('(Node/Name)[2]','varchar(10)') Name2, 
    A.x.value('(Node/Name)[3]','varchar(10)') Name3 
FROM @xml.nodes('Person') AS A(x)

/*
Name1      Name2      Name3
---------- ---------- ----------
admin      lisi       wangwu

(1 行受影响)
*/

-------------------------------------------------------------------

想写一个存储过程，将一个XML文本作为参数输入
XML如：
<?xml version="1.0" encoding="utf-8" ?>
<abcs>
<abc QueryName="LatestExchange">
<QueryColumns>
<Columna>FromCurrency </Columna>
<Columna>ToCurrency </Columna>
<Columna>Date </Columna>
<Columna>Value </Columna>
<Columna>OpenPrice </Columna>
<Columna>HighPrice </Columna>
<Columna>LowPrice </Columna>
<Columna>ClosePrice </Columna>
<Columna>Volume </Columna>
<Columna>OpenInterest </Columna>
<Columna>LastUpdate </Columna>
</QueryColumns>
<QueryParameters>
<Parameter Name="Date"> </Parameter>
</QueryParameters>
</abc>
</abcs>

---------------------------------------
问如何查询，才能得到以下结果

Columna
-----------
ToCurrency
Date
Value
OpenPrice
HighPrice
LowPrice
ClosePrice
Volume
OpenInterest
LastUpdate

-->小梁

DECLARE @doc xml;
SET @doc='<?xml version="1.0" encoding="utf-8" ?>
<abcs>
  <abc QueryName="LatestExchange">
    <QueryColumns>
      <Columna>FromCurrency </Columna>
      <Columna>ToCurrency </Columna>
      <Columna>Date </Columna>
      <Columna>Value </Columna>
      <Columna>OpenPrice </Columna>
      <Columna>HighPrice </Columna>
      <Columna>LowPrice </Columna>
      <Columna>ClosePrice </Columna>
      <Columna>Volume </Columna>
      <Columna>OpenInterest </Columna>
      <Columna>LastUpdate </Columna>
    </QueryColumns>
    <QueryParameters>
      <Parameter Name="Date" />
    </QueryParameters>
  </abc>
</abcs>'

SELECT
    A.x.value('.','varchar(20)')
FROM @doc.nodes('/abcs/abc/QueryColumns/*[position()>1]') AS A(x);

/*
--------------------
ToCurrency
Date
Value
OpenPrice
HighPrice
LowPrice
ClosePrice
Volume
OpenInterest
LastUpdate

(10 行受影响)
*/

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/04/01/4040080.aspx