使用 XQuery 获取这些数据

2021-10-02 00:00:00 xquery sql-server-2008 sql-server xquery-sql

我是 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

相关文章