查找 XML 中的所有名字 [英] Find all the first names in an XML

查看：27
本文介绍了查找 XML 中的所有名字的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

名为 AdvantureWorks2012 的 Microsoft 实践数据库有一个名为 HumanResources.JobCandidate 的表.该表有几列.其中一列是 uri 数据类型.这是该列中一个单元格内的内容:

Microsoft practice database called AdvantureWorks2012 has a table called HumanResources.JobCandidate. That table has several columns. One of those columns is of a uri datatype. This is what's inside one of the cells in that column:

<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">
  <ns:Name>
    <ns:Name.Prefix></ns:Name.Prefix>
    <ns:Name.First>Shai</ns:Name.First>
    <ns:Name.Middle></ns:Name.Middle>
    <ns:Name.Last>Bassli</ns:Name.Last>
    <ns:Name.Suffix></ns:Name.Suffix>
  </ns:Name>
  <ns:Skills>
I am an experienced and versatile machinist who can operate a range of machinery personally as well as supervise the work of other machinists. I specialize in diagnostics and precision inspection, have expertise in reading blueprints, and am able to call on strong interpersonal and communication skills to guide the work of other production machinists whose work I am called upon to inspect. 
My degree in mechanical engineering affords me a better theoretical understanding and mathematical background than many other candidates in the machinist trade.
    </ns:Skills>
  <ns:Employment>
    <ns:Emp.StartDate>2000-06-01Z</ns:Emp.StartDate>
    <ns:Emp.EndDate>2002-09-30Z</ns:Emp.EndDate>
    <ns:Emp.OrgName>Wingtip Toys</ns:Emp.OrgName>
    <ns:Emp.JobTitle>Lead Machinist</ns:Emp.JobTitle>
    <ns:Emp.Responsibility> Supervised work of staff of four machinists. Coordinated all complex assembly and tooling activities, including production of tricycles and wagons.
Developed parts fabrication from sample parts, drawings and verbal orders.Worked with ISO9000 implementation.
        </ns:Emp.Responsibility>
    <ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory>
    <ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory>
    <ns:Emp.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>MI </ns:Loc.State>
        <ns:Loc.City>Saginaw</ns:Loc.City>
      </ns:Location>
    </ns:Emp.Location>
  </ns:Employment>
  <ns:Employment>
    <ns:Emp.StartDate>1996-11-15Z</ns:Emp.StartDate>
    <ns:Emp.EndDate>2000-05-01Z</ns:Emp.EndDate>
    <ns:Emp.OrgName>Blue Yonder Airlines</ns:Emp.OrgName>
    <ns:Emp.JobTitle>Machinist</ns:Emp.JobTitle>
    <ns:Emp.Responsibility>Repaired and maintained a variety of production and fabrication machine tools.
Set up and operated machines to close tolerances. Used and wrote CNC machine programs. Trained extensively in computer-aided manufacturing.
        </ns:Emp.Responsibility>
    <ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory>
    <ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory>
    <ns:Emp.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>IL </ns:Loc.State>
        <ns:Loc.City>Chicago</ns:Loc.City>
      </ns:Location>
    </ns:Emp.Location>
  </ns:Employment>
  <ns:Employment>
    <ns:Emp.StartDate>1994-06-10Z</ns:Emp.StartDate>
    <ns:Emp.EndDate>1996-07-22Z</ns:Emp.EndDate>
    <ns:Emp.OrgName>City Power and Light</ns:Emp.OrgName>
    <ns:Emp.JobTitle>Assistant Machinist</ns:Emp.JobTitle>
    <ns:Emp.Responsibility>Performed centerless grinding. Received training in manual mill and lathe machines, as well as micrometers and calipers.
Owned complete toolset.Worked extensive overtime on request. </ns:Emp.Responsibility>
    <ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory>
    <ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory>
    <ns:Emp.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>IA </ns:Loc.State>
        <ns:Loc.City>Des Moines</ns:Loc.City>
      </ns:Location>
    </ns:Emp.Location>
  </ns:Employment>
  <ns:Education>
    <ns:Edu.Level>Bachelor</ns:Edu.Level>
    <ns:Edu.StartDate>1990-09-15Z</ns:Edu.StartDate>
    <ns:Edu.EndDate>1994-05-10Z</ns:Edu.EndDate>
    <ns:Edu.Degree>Bachelor of Science</ns:Edu.Degree>
    <ns:Edu.Major>Mechanical Engineering</ns:Edu.Major>
    <ns:Edu.Minor></ns:Edu.Minor>
    <ns:Edu.GPA>3.2</ns:Edu.GPA>
    <ns:Edu.GPAScale>4</ns:Edu.GPAScale>
    <ns:Edu.School>Midwest State University</ns:Edu.School>
    <ns:Edu.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>IA </ns:Loc.State>
        <ns:Loc.City>Ames</ns:Loc.City>
      </ns:Location>
    </ns:Edu.Location>
  </ns:Education>
  <ns:Address>
    <ns:Addr.Type>Home</ns:Addr.Type>
    <ns:Addr.Street>567 3rd Ave</ns:Addr.Street>
    <ns:Addr.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>MI </ns:Loc.State>
        <ns:Loc.City>Saginaw</ns:Loc.City>
      </ns:Location>
    </ns:Addr.Location>
    <ns:Addr.PostalCode>53900</ns:Addr.PostalCode>
    <ns:Addr.Telephone>
      <ns:Telephone>
        <ns:Tel.Type>Voice</ns:Tel.Type>
        <ns:Tel.IntlCode>1</ns:Tel.IntlCode>
        <ns:Tel.AreaCode>276</ns:Tel.AreaCode>
        <ns:Tel.Number>555-0114</ns:Tel.Number>
      </ns:Telephone>
      <ns:Telephone>
        <ns:Tel.Type>Fax</ns:Tel.Type>
        <ns:Tel.IntlCode>1</ns:Tel.IntlCode>
        <ns:Tel.AreaCode>276</ns:Tel.AreaCode>
        <ns:Tel.Number>555-0132</ns:Tel.Number>
      </ns:Telephone>
    </ns:Addr.Telephone>
  </ns:Address>
  <ns:EMail>Shai@Example.com</ns:EMail>
  <ns:WebSite></ns:WebSite>
</ns:Resume>

我需要在此列中找到所有起诉 tsql 的名字和姓氏.我只是一个初学者，所以详细的解释会有很长的路要走.谢谢.

I need to find all first and last names in this column suing tsql. I am just a beginner, so an elaborate explanation would go long ways. Thanks.

推荐答案

第一步，注册默认命名空间 URI(这部分 XML : xmlns="...") :

First step, register default namespace URI (this part of your XML : xmlns="...") :

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')

然后您可以在 XML 列上使用 value() 方法，将 XQuery 作为第一个参数，将 SQL 数据类型作为第二个参数，例如:

then you can use value() method on the XML column passing XQuery as first parameter and SQL data type as the second, for example :

Resume.value('(/Resume/Name/Name.First)[1]', 'varchar(100)') AS 'First'

以上 XQuery/Xpath 分解:

Above XQuery/Xpath break down :

  • (....)[1] :获取内部 XQuery/XPath(.... 部分)的第一个结果.
  • /Resume/Name/Name.First :在这种情况下，使用的 XQuery/XPath 只是一个简单的路径表达式，就像我们 PC 中的文件/文件夹路径.但是这个 XPath/XQuery 不是在存储中表达文件/文件夹的位置，而是在整个 XML 文档中表达 XML 节点位置.
  • (....)[1] : Get the first result of the inner XQuery/XPath (the .... part).
  • /Resume/Name/Name.First : In this case, the XQuery/XPath used is just a simple path expression, like file/folder path in our PC. But instead of expressing file/folder location within storage, this XPath/XQuery expresses XML node location within the entire XML document.

完整的查询示例如下所示:

Complete query example looks about like this :

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')

select 
  Resume.value('(/Resume/Name/Name.First)[1]', 'varchar(100)') AS 'First',
  Resume.value('(/Resume/Name/Name.Last)[1]', 'varchar(100)') AS 'Last'
from HumanResources.JobCandidate

供参考:

  • MSDN: value() Method (xml Data Type)
  • MSDN: Add Namespaces to Queries with WITH XMLNAMESPACES

这篇关于查找 XML 中的所有名字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆