xquery_在SQL Server中使用XQuery筛选XML列

xquery

XQuery in the SQL Server helps to query and extract data from XML documents. XQuery gives different approaches to get information from the XML document and the equivalent can be used on applying a data filter or where clause on XML elements as well.

SQL Server中的XQuery有助于查询和提取XML文档中的数据。 XQuery提供了不同的方法来从XML文档中获取信息，并且等效方法也可以用于在XML元素上应用数据过滤器或where子句。

SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column. SQL Server 2005 or later editions, allows using XML data typed input parameter in the stored procedure and supports using different XML functions to extract the information from XML nodes and vice versa with getting attributed in the XML data type column as well.

SQL Server提供XQuery功能来查询XML数据类型或使用带有XPATH的XML列进行查询。 使用XQuery，用户可以在XML列中使用XML节点和节点值插入，更新和删除。 SQL Server 2005或更高版本允许在存储过程中使用XML数据类型的输入参数，并支持使用不同的XML函数从XML节点提取信息，反之亦然，并且在XML数据类型列中也具有属性。

In this article, we will exercise with the Querying XML data type using XQuery to channel the information with the XML nodes and attributes with a few examples. Here, we have utilized one example table with sample data for a better explanation.

在本文中，我们将使用XQuery来使用Querying XML数据类型进行练习，以通过一些示例将信息传递给XML节点和属性。 在这里，我们利用了一个带有示例数据的示例表来进行更好的解释。

Sample XML:

样本XML：

<EmployeeDetails>
  <BusinessEntityID>3</BusinessEntityID>
  <NationalIDNumber>509647174</NationalIDNumber>
  <JobTitle>Engineering Manager</JobTitle>
  <BirthDate>1974-11-12</BirthDate>
  <MaritalStatus>M</MaritalStatus>
  <Gender>M</Gender>
  <StoreDetail>
    <Store>
      <AnnualSales>800000</AnnualSales>
      <AnnualRevenue>80000</AnnualRevenue>
      <BankName>Guardian Bank</BankName>
      <BusinessType>BM</BusinessType>
      <YearOpened>1987</YearOpened>
      <Specialty>Touring</Specialty>
      <SquareFeet>21000</SquareFeet>
    </Store>
    <Store>
      <AnnualSales>300000</AnnualSales>
      <AnnualRevenue>30000</AnnualRevenue>
      <BankName>International Bank</BankName>
      <BusinessType>BM</BusinessType>
      <YearOpened>1982</YearOpened>
      <Specialty>Road</Specialty>
      <SquareFeet>9000</SquareFeet>
    </Store>
  </StoreDetail>
</EmployeeDetails>

从目标XML节点获取值 (Fetching value from target XML node)

The tabular format from an XML document with the bifurcation of XML node as the column name utilizing the SQL Server can be accomplished with the help of XQuery. XQuery will return the XML node value in the tabular model with a combination of nodes() and value() function on the XML column. nodes() function acknowledges XML node location as