問題描述

我是 XQuery 的新手，但遇到了一些問題.這是我的例子.

I am new to XQuery and am having some problems with it. Here is my example.

我有這個(gè)變量:

declare @xmlDoc XML

其中存儲(chǔ)了以下 xml:

it has the following xml stored in it:

<?xml version="1.0" encoding="utf-8"?>
<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Table1">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="Sharedparam" type="xs:string" minOccurs="0" />
                <xs:element name="Antoher" type="xs:string" minOccurs="0" />
                <xs:element name="RandomParam2" type="xs:string" minOccurs="0" />
                <xs:element name="MoreParam" type="xs:string" minOccurs="0" />
                <xs:element name="ResultsParam" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Table1>
    <Sharedparam>shared</Sharedparam>
    <Antoher>sahre</Antoher>
    <RandomParam2>Good stuff</RandomParam2>
    <MoreParam>and more</MoreParam>
    <ResultsParam>2</ResultsParam>
  </Table1>
  <Table1>
    <Sharedparam>Hey</Sharedparam>
    <Antoher>what </Antoher>
    <RandomParam2>do you</RandomParam2>
    <MoreParam>think</MoreParam>
    <ResultsParam>2</ResultsParam>
  </Table1>
  <Table1 />
</NewDataSet>

如何選擇 Sharedparam 的所有值?(或者真的任何返回值(不是 xml)的體面的查詢都會(huì)很棒.)

How can I select all the values of Sharedparam? (Or really any decent query that returns values (not xml) would be great.)

我真正想做的是得到這樣的結(jié)果集:

What I am really looking to do is get a result set like this:

Name             Value1          Value2          Value3        Value4
Sharedparam      shared          Hey             Null          Null
Another          share           what            Null          Null
....

這會(huì)讓我忽略Value4"之外的任何數(shù)據(jù)(這對(duì)于我使用這些數(shù)據(jù)是可以接受的).

This would have me ignoring any data beyond "Value4" (and that is acceptable for my use of this data).

推薦答案

試試這個(gè):

SELECT
    TBL.SParam.value('(.)[1]', 'varchar(50)')
FROM
    @xmldoc.nodes('/NewDataSet/Table1/Sharedparam') AS TBL(SParam)

給我一??個(gè)輸出:

(No column name)
shared
Hey

更新:如果您想獲得 <Table1> 元素中的所有 XML 元素及其值，您可以使用這個(gè) XQuery:

Update: if you want to get at all the XML elements and their values inside the <Table1> elements, you can use this XQuery:

SELECT
    TBL.SParam.value('local-name(.)[1]', 'varchar(50)') 'Attribute',
    TBL.SParam.value('(.)[1]', 'varchar(50)') 'Value'
FROM
    @xmldoc.nodes('/NewDataSet/Table1/*') AS TBL(SParam)

輸出:

Attribute            Value
Sharedparam          shared
Antoher              sahre
RandomParam2         Good stuff
MoreParam            and more
ResultsParam         2
Sharedparam          Hey
Antoher              what 
RandomParam2         do you
MoreParam            think
ResultsParam         2

更新 #2: 獲取第一個(gè) 和第二個(gè) XML 節(jié)點(diǎn)旁邊的值彼此之間，您需要對(duì) .nodes() 進(jìn)行兩次調(diào)用 - 一次檢索第一個(gè)節(jié)點(diǎn)，另一次檢索第二個(gè).它有點(diǎn)毛茸茸的，特別是如果你想進(jìn)一步擴(kuò)展它 - 性能會(huì)很糟糕 - 但它有效:-)

Update #2: to get the values of the first <Table1> and the second <Table1> XML node next to one another, you need to do two calls to .nodes() - once retrieving the first node, the other time the second one. It gets a bit hairy, especially if you want to extend that even further - and performance is going to be abysmal - but it works :-)

SELECT
    TBL.SParam.value('local-name(.)[1]', 'varchar(50)') 'Attribute',
    TBL.SParam.value('(.)[1]', 'varchar(50)') 'Value 1',
    TBL2.SParam2.value('(.)[1]', 'varchar(50)') 'Value 2'
FROM
    @xmldoc.nodes('/NewDataSet/Table1[1]/*') AS TBL(SParam)
INNER JOIN
    @xmldoc.nodes('/NewDataSet/Table1[2]/*') AS TBL2(SParam2) ON TBL.SParam.value('local-name(.)[1]', 'varchar(50)') = TBL2.SParam2.value('local-name(.)[1]', 'varchar(50)')

給出以下輸出:

Attribute      Value 1     Value 2
Sharedparam    shared       Hey
ResultsParam      2          2
RandomParam2   Good stuff   do you
Antoher        sahre        what 
MoreParam      and more     think

這篇關(guān)于使用 XQuery 獲取這些數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！