問題描述

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

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

我有這個變量:

declare @xmlDoc XML

其中存儲了以下 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)的體面的查詢都會很棒.)

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

我真正想做的是得到這樣的結果集:

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
....

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

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

推薦答案

試試這個:

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

給我一??個輸出:

(No column name)
shared
Hey

更新:如果您想獲得 <Table1> 元素中的所有 XML 元素及其值，您可以使用這個 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: 獲取第一個 和第二個 XML 節點旁邊的值彼此之間，您需要對 .nodes() 進行兩次調用 - 一次檢索第一個節點，另一次檢索第二個.它有點毛茸茸的，特別是如果你想進一步擴展它 - 性能會很糟糕 - 但它有效:-)

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

這篇關于使用 XQuery 獲取這些數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！