使用节点（）和值（）获取父值

I have an XML document snippet that matches this XSD:

我有一个与此XSD匹配的XML文档片段:

<xs:complexType name="QuestionType">
  <xs:sequence>
    <xs:element name="questionId" type="xs:string" minOccurs="1" />
    <xs:element name="questionDescription" type="xs:string" minOccurs="1" />
    <xs:element name="questionHeader" type="xs:string" minOccurs="0" />
    <xs:element name="questionLabel" type="xs:string" minOccurs="0" />
    <xs:element name="version" type="xs:string" minOccurs="1" maxOccurs="1" />
    <xs:element name="SubQuestion" type="QuestionType"
                minOccurs="0" maxOccurs="unbounded" />
  </xs:sequence>
</xs:complexType>

This recursively defines a <Question> elements that can have an infite number of <SubQuestion> elements, both of the type QuestionType.

这递归地定义了一个 元素,它们可以有一个非常多的 元素,这两个元素都是QuestionType类型。

Using SQL, I'd like to query the document once to get a single result set with all of the questions and sub-questions. I have two independent queries at the moment to achieve this (please note that I'm using NVarChar(1000) for testing purposes only - they will be more appropriately sized in production, and that @X is an XML variable that matches the schema above):

使用SQL,我想查询文档一次,以获得包含所有问题和子问题的单个结果集。我目前有两个独立的查询来实现这一点(请注意我只使用NVarChar(1000)进行测试 - 它们在生产中的尺寸更合适,并且@X是一个与上述模式匹配的XML变量):

SELECT -- Top-level questions...
  C.value('questionId[1]', 'NVarChar(1000)') Id,
  NULL ParentId,
  C.value('questionDescription[1]', 'NVarChar(1000)') Description,
  NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header,
  NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label,
  C.value('version[1]', 'NVarChar(1000)') Version
FROM @X.nodes('//Question') X(C);

SELECT -- Sub-questions...
  C.value('questionId[1]', 'NVarChar(1000)') Id,
  C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)') ParentId,
  C.value('questionDescription[1]', 'NVarChar(1000)') Description,
  NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header,
  NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label,
  C.value('version[1]', 'NVarChar(1000)') Version
FROM @X.nodes('//SubQuestion') X(C);

I'd expect this could be solved using a recursive CTE, but I'm having trouble putting one together.

我希望这可以通过递归CTE来解决,但是我很难将它们组合在一起。

3 个解决方案

#1

Given that you have tagged this question with sql-server-2008 and that IMHO SQL Server 2008 has support for XQuery I would like to suggest a different "angle": use an XPath expression to select the nodes you are interested in.

鉴于您已使用sql-server-2008标记了此问题,并且IMHO SQL Server 2008支持XQuery,我想建议一个不同的“角度”:使用XPath表达式选择您感兴趣的节点。

.//*[local-name(.) = 'Question' or local-name(.) = 'SubQuestion']

Please note that I am using the XPath function local-name() in case your real XML data has namespace declarations.

请注意,我正在使用XPath函数local-name(),以防您的真实XML数据具有名称空间声明。

I have created a sample XML file to test the expression above:

我创建了一个示例XML文件来测试上面的表达式:

<?xml version="1.0" encoding="UTF-8"?>
<Questions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns="http://www.acme.com" xsi:schemaLocation="sample.xsd">
    <Question>
        <questionId>1</questionId>
        <questionDescription>Question 1</questionDescription>
        <version>1</version>
        <SubQuestion>
            <questionId>1.1</questionId>
            <questionDescription>Question 1.1</questionDescription>
            <version>1</version>
            <SubQuestion>
                <questionId>1.1.1</questionId>
                <questionDescription>Question 1.1.1</questionDescription>
                <version>1</version>
                <SubQuestion>
                    <questionId>1.1.1.1</questionId>
                    <questionDescription>Question 1.1.1.1</questionDescription>
                    <version>1</version>
                </SubQuestion>
                <SubQuestion>
                    <questionId>1.1.1.2</questionId>
                    <questionDescription>Question 1.1.1.2</questionDescription>
                    <version>1</version>
                </SubQuestion>
            </SubQuestion>
            <SubQuestion>
                <questionId>1.2</questionId>
                <questionDescription>Question 1.2</questionDescription>
            </SubQuestion>
        </SubQuestion>
    </Question>
    <Question>
        <questionId>2</questionId>
        <questionDescription>Question 2</questionDescription>
        <version>1</version>
    </Question>
    <Question>
        <questionId>3</questionId>
        <questionDescription>Question 3</questionDescription>
        <version>1</version>
        <SubQuestion>
            <questionId>3.1</questionId>
            <questionDescription>Question 3.1</questionDescription>
            <version>1</version>
        </SubQuestion>
    </Question>
</Questions>

Evaluating this XQuery query against that sample

针对该示例评估此XQuery查询

declare namespace acme = "http://www.acme.com";
<AllQuestions>
 {
   for $question in .//*[local-name(.) = 'Question' or local-name(.) = 'SubQuestion']
   return
        <Question>
            <questionId>{ data($question/acme:questionId) }</questionId>
            <questionDescription>{ data($question/acme:questionDescription) }</questionDescription>
        </Question>
 }
</AllQuestions>

will result in

会导致

<?xml version="1.0" encoding="UTF-8"?>
<AllQuestions>
   <Question>
      <questionId>1</questionId>
      <questionDescription>Question 1</questionDescription>
   </Question>
   <Question>
      <questionId>1.1</questionId>
      <questionDescription>Question 1.1</questionDescription>
   </Question>
   <Question>
      <questionId>1.1.1</questionId>
      <questionDescription>Question 1.1.1</questionDescription>
   </Question>
   <Question>
      <questionId>1.1.1.1</questionId>
      <questionDescription>Question 1.1.1.1</questionDescription>
   </Question>
   <Question>
      <questionId>1.1.1.2</questionId>
      <questionDescription>Question 1.1.1.2</questionDescription>
   </Question>
   <Question>
      <questionId>1.2</questionId>
      <questionDescription>Question 1.2</questionDescription>
   </Question>
   <Question>
      <questionId>2</questionId>
      <questionDescription>Question 2</questionDescription>
   </Question>
   <Question>
      <questionId>3</questionId>
      <questionDescription>Question 3</questionDescription>
   </Question>
   <Question>
      <questionId>3.1</questionId>
      <questionDescription>Question 3.1</questionDescription>
   </Question>
</AllQuestions>

EDIT - Final Query

编辑 - 最终查询

SELECT
    C.value('questionId[1]', 'NVarChar(1000)') Id,
    COALESCE(
      C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)'),
      C.query('..').value('(SubQuestion/questionId)[1]', 'NVarChar(1000)')
    ) ParentId,
    C.value('questionDescription[1]', 'NVarChar(1000)') Description,
    NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header,
    NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label,
    C.value('version[1]', 'NVarChar(1000)') Version
FROM
  @X.nodes('.//*[local-name(.)="Question" or local-name(.)="SubQuestion"]') X(C);

#2

I'm doing this so far, although I'm still hoping to compact the query a bit:

我到目前为止这样做,虽然我仍然希望稍微压缩查询:

WITH Q AS (
  SELECT
    C.value('questionId[1]', 'NVarChar(1000)') Id,
    NULL ParentId,
    C.value('questionDescription[1]', 'NVarChar(1000)') Description,
    NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header,
    NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label,
    C.value('version[1]', 'NVarChar(1000)') Version
  FROM @X.nodes('//Question') X(C)
  UNION ALL
  SELECT
    C.value('questionId[1]', 'NVarChar(1000)') Id,
    COALESCE(
      C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)'),
      C.query('..').value('(SubQuestion/questionId)[1]', 'NVarChar(1000)')
    ) ParentId,
    C.value('questionDescription[1]', 'NVarChar(1000)') Description,
    NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header,
    NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label,
    C.value('version[1]', 'NVarChar(1000)') Version
  FROM @X.nodes('//SubQuestion') X(C)
)
SELECT Q.Id, Q.ParentId, Q.Description, Q.Header, Q.Label, Q.Version
FROM Q;

This is the important bit, as it will get whichever is the first non-null ParentId value:

这是重要的一点,因为它将获得第一个非null ParentId值:

COALESCE(
  C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)'),
  C.query('..').value('(SubQuestion/questionId)[1]', 'NVarChar(1000)')
) ParentId

#3

You can use a CTE:

您可以使用CTE:

WITH TopLevel (ID, ParentID, Description, Header, Label,Level)
AS
(
SELECT -- Top-level questions...
  C.value('questionId[1]', 'NVarChar(1000)') Id,
  NULL ParentId,
  C.value('questionDescription[1]', 'NVarChar(1000)') Description,
  NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header,
  NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label,
  C.value('version[1]', 'NVarChar(1000)') Version,
  0 AS Level
FROM @X.nodes('//Question') X(C)
UNION ALL
SELECT -- Sub-questions...
  C.value('questionId[1]', 'NVarChar(1000)') Id,
  C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)') ParentId,
  C.value('questionDescription[1]', 'NVarChar(1000)') Description,
  NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header,
  NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label,
  C.value('version[1]', 'NVarChar(1000)') Version
  ,Level + 1 AS Level
FROM @X.nodes('//SubQuestion') X(C)
JOIN TopLevel AS t ON C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)') = t.id )

SELECT * FROM TopLevel

Reference: http://msdn.microsoft.com/en-us/library/ms186243.aspx