天道酬勤,学无止境

从 MS-SQL Server 2008 中的 XML 字段中提取值(Extracting values from XML field in MS-SQL Server 2008)

问题

我正在尝试从存储在单个 XML 字段中的 MS-SQL Server 2008 数据库中提取四条信息。 这是我第一次不得不使用 XML,所以我遇到了一些麻烦,这就是为什么我只有我要提取的数据。 我曾尝试使用其他帖子来解决我的问题,但显然没有运气。

这四条信息首先是“项目经理”,然后是“价值”,然后是“利润中心”,然后是价值。 “利润中心”的值将用于在两个表之间进行连接。 下面是存储在此字段中的 XML 数据示例。

    <ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <EntityPropertyOfString>
       <Name>Project Manager</Name>
       <Value>DBD</Value>
     </EntityPropertyOfString>
     <EntityPropertyOfString>
       <Name>Profit Center</Name>
       <Value>211</Value>
     </EntityPropertyOfString>
    </ArrayOfEntityPropertyOfString>

所以在这个例子中,我需要使用“利润中心”的值“211”在一个 MS-SQL 查询中加入这两个表。 该信息可以称为“tblProfitCenter”的表和保存它的字段“prftData”。

这是一个组合查询,如果“prftData”中的数据不是 XML 格式,而是一个包含利润中心 ID 并执行连接的常规整数字段,则该查询将执行相同的工作。

    SELECT md.LName, md.FName, pc.ProfitCenterName
    FROM tblMainDataCenter md
    LEFT OUTER JOIN tblProfitCenter pc ON md.pcID = pc.prftData

这是针对我工作的项目,需要能够超越这一点。 通常我会学习 XML 来解决这个问题,但时间不允许这样做。 在我有机会学习 XML 之前,我将不胜感激。

回答1

您可以使用 XML 列上的value函数从 XML 中提取数据,如下所示:

SELECT col.value('(/ArrayOfEntityPropertyOfString/EntityPropertyOfString[Name="Profit Center"]/Value)[1]', 'int')
FROM tbl

假设您的表名为tbl并且 XML 列名为col

第一个参数是 XPath 1 表达式,第二个参数是目标数据类型。 请注意,这些字符串必须是 SQL Server 中的固定字符串,并且不能动态构建,例如通过字符串连接。

回答2

以下查询将允许您获取 xml 内容并将它们以表格格式放置,以便您随后可以对该表格执行必要的 sql 操作:

Declare @xmlstring xml = '<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <EntityPropertyOfString>
       <Name>Project Manager</Name>
       <Value>DBD</Value>
     </EntityPropertyOfString>
     <EntityPropertyOfString>
       <Name>Profit Center</Name>
       <Value>211</Value>
     </EntityPropertyOfString>
    </ArrayOfEntityPropertyOfString>'


select MainDataCenter.Col.value('(Name)[1]','varchar(max)') as Name
,MainDataCenter.Col.value('(Value)[1]','varchar(max)') as Value
from @xmlstring.nodes('/ArrayOfEntityPropertyOfString/EntityPropertyOfString') as MainDataCenter(Col)

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 从SQL Server 2008表中的XML字段中提取属性(Extracting Attributes from XML Fields in SQL Server 2008 Table)
    问题 我有一个包含几列的表,其中一列是xml列。 我没有要在查询中使用的名称空间。 所有记录的XML数据始终是相同的结构。 人为数据 create table #temp (id int, name varchar(32), xml_data xml) insert into #temp values (1, 'one', '<data><info x="42" y="99">Red</info></data>'), (2, 'two', '<data><info x="27" y="72">Blue</info></data>'), (3, 'three', '<data><info x="16" y="51">Green</info></data>'), (4, 'four', '<data><info x="12" y="37">Yellow</info></data>') 所需结果 Name Info.x Info.y Info ----- ------- ------- ------- one 42 99 Red two 27 72 Blue three 16 51 Green four 12 37 Yellow 部分作品 select Name, xml_data.query('/data/info/.').value('.', 'varchar(10)') as
  • Extracting values from XML field in MS-SQL Server 2008
    I am trying to extract four pieces of information from a MS-SQL Server 2008 database which is stored in a single XML field. This is the first time I have ever had to work with XML so I am having a bit of trouble and which is why I only have the data I am trying to extract. I have tried using other postings to solve my problem, but obviously with no luck. The four pieces of information is first the "Project Manager" and then the "Value" and then 2nd is the "Profit Center" and then that value. The value from the "Profit Center" is going to be used to do a join between two tables. Below is a
  • 从SQL Server 2008中的XML字段中选择值(Select values from XML field in SQL Server 2008)
    问题 仅查看我的XML字段,我的行将如下所示: <person><firstName>Jon</firstName><lastName>Johnson</lastName></person> <person><firstName>Kathy</firstName><lastName>Carter</lastName></person> <person><firstName>Bob</firstName><lastName>Burns</lastName></person> 请注意,这些是我表中的三行。 我想以表格形式返回SQL结果 Jon | Johnson Kathy| Carter Bob | Burns 什么查询可以完成此任务? 回答1 假设XML字段名为“ xmlField” ... SELECT [xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName, [xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName FROM [myTable] 回答2 考虑到XML数据来自表'table'并存储在'field'列中:使用XML方法,使用xml.value()提取值,使用xml
  • 从 Sql Server 列中的 XML 中提取数据(Extracting data from XML in Sql Server column)
    问题 我刚刚发现(必要时)TSQL 有一些功能可以从包含 XML 的列中提取数据。 我有一个包含 XML 数据的 Sql Server 列(尽管列类型不是 xml...它是 varchar)。 我应该从列中提取数据的子集。 一个简化的条目看起来像这样: declare @Table as table(id char(1), datacolumn xml) insert into @table select 'a', '<root><vnode v="5" /><vnode v="8" /></root>' insert into @table select 'b', '<root><vnode v="7" /></root>' id datacolumn --- ----------------------------------------- a <root><vnode v="5" /><node v="8" /></root> b <root><vnode v="7" /></root> 因此,我想提出一个查询,该查询将返回以下内容: id data --- ---- a 5 a 8 b 7 我已经到了能够获得一些信息的地步……但是我缺少一些概念。 select id, T.c.value('node[1]', 'varchar(100)') mdata from
  • 提取存储在 SQL 列中的由“:”分隔的字符串的第 n 个字段(Extracting nth field of string delimited by ":" stored in a SQL column)
    问题 我有一个包含两个以下列的 SQL 表: FORMAT Sample GT:AD:DP:GQ:PL 0/0:233,0:233:99:0,120,1800 GT:AD:DP:GQ:PL 0/1:101,61:220:99:835,0,1859 GT:AD:DP:GQ:PL 0/0:172,0:172:99:0,120,1800 GT:AD:DP:GQ:PL 0/0:216,0:216:99:0,120,1800 GT:AD:DP:GQ:PL 0/0:216,0:216:99:0,120,1800 GT:AD:DP:GQ:PGT:PID:PL 0/1:185,232:417:99:0|1:8029494_T_G:8670,0,6429 GT:AD:DP:GQ:PL 0/0:367,0:367:99:0,120,1800 GT:AD:DP:GQ:PGT:PID:PL 0/1:150,198:348:99:0|1:8029494_T_G:7930,0,5677 GT:AD:DP:GQ:PGT:PID:PL 0/1:148,196:344:99:0|1:8029494_T_G:7876,0,5652 GT:AD:DP:GQ:PGT:PID:PL 0/0:148,0:344:99:0|1:8029494_T_G:7876,8334,14591 GT:AD:DP:GQ:PGT:PID:PL
  • 如何在 SQL Server 2008 中读取 XML 列?(How to read XML column in SQL Server 2008?)
    问题 我从未在 SQL Server 2008 中使用过 XML,我需要将字段列表提取到一个变量表中,你怎么做? 鉴于我在 XMLMain 表中有一个名为 xmldata 的列,它看起来像下面这样,如何提取 sql 中的字段列表? ![在此处输入图像描述][1] <?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:SampleForm:-myXSD-2014-03-29T09-41-23" solutionVersion="1.0.0.18" productVersion="15.0.0.0" PIVersion="1.0.0.0" href="http://bipc20/sites/team-1303/FormServerTemplates/SampleForm.xsn"?> <?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.4"?> <my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003
  • 提取信息。 从 XML 到 Cocoa(Extracting info. from XML into Cocoa)
    问题 我正在尝试解析 XML 以提取某些变量的值。 下面是一个例子: <?xml version='1.0'?> <Main xmlns='http://www.abc.uk' version='1.0' name='full'> <child1 version='2.0'> <value1> xyz </value1> <userinfo> <name> joe </name> <pass> joepass </pass> </userinfo> </child1> </Root> 问题:如何提取要显示的“xyz”值? 如何提取 'joe' 和 'joepass' 来显示? 根据我的理解,child1 是具有“version”属性的根。 'value1' 和 'userinfo' 都是元素。 在 Cocoa 中,我将如何显示这些值? 我可以做一个 [child elementsForName:@"userinfo" 并显示所有值。 我需要专门提取'joe'和'joepass'。 谢谢。 回答1 如何提取要显示的“xyz”值? 如何提取 'joe' 和 'joepass' 来显示? 像这样的东西。 这假设您在NSString有您的 XML: NSXMLDocument* xmlDoc; NSError* error = nil; NSUInteger options =
  • 使用xPath的Powershell脚本中的.SelectSingleNode无法从web.config文件中提取值(.SelectSingleNode in Powershell script using xPath not working on extracting values from web.config file)
    问题 好的,这是我的web.config文件的代码段: <?xml version="1.0" encoding="utf-8"?> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <location path="." inheritInChildApplications="false"> <connectionStrings> ... </connectionStrings> </location> <location path="." inheritInChildApplications="false"> <appSettings> <!--IT Ops--> <add key="SomeOtherKey" value="SomeOtherValue" /> <add key="SiteDomain" value="somedomain.com" /> <add key="SomeOtherKey" value="SomeOtherValue" /> .... </appSettings> </location> </configuration> 我正在尝试通过Powershell使用xPath查找节点。 关于此XML文件的几点注意事项: 有多个: <location
  • SQL Server 2008 中通过匹配多个表来提取数据(Extracting data by matching multiple tables in SQL Server 2008)
    问题 我想从多个表中提取基于匹配的结果,具体来说是三个表。 这些表是Despatch 、 Activation和Replaced 。 下图显示了其中的结构和示例数据。 基于上图的预期结果是: LotQty | ApprovedQty | DispatchDate | Installed 15 | 10 | 2013-8-7 | 9 结果应符合以下标准: LotQty、ApprovedQty和DispatchDate只是计数,与Despatch表不同 已安装(真正的问题)。 该字段基于计算: 查询应首先将Despatch表中 Lot 20 的SerialNo与Activation表中 Lot 20 下的SerialNo匹配。 它找到了 8 个匹配,忽略了具有不同批次数量的 Product1&2。 接下来对于 8 个匹配的 SerialNo,它应该比较日期。 如果SerialNo DispatchDate小于ActivationDate那么它被算作 1。(Product3 的DispatchDate大于ActivationDate 。因此它被忽略。对于 SerialNo 的DispatchDate其余部分是 < ActivationDate )。 因此, Activation表的总结果数为 7。 对于Despatch表(Product1,2 & 3)中不匹配的 SerialNo
  • 在 Shell 中从文件中提取字段(extracting fields from a file in Shell)
    问题 我的文件中有很多数据,如下所示 alert tcp any any -> any any (msg: "test1"; sid:16521; rev:1;created_at 2010_07_30, updated_at 2016_07_01;) alert tcp any any -> any any (msg: "test2"; nocase; sid :23476;distance:0; rev:1;created_at 2010_10_30, updated_at 2013_07_11;) alert tcp any any -> any any (msg: "test3"; sid:236487; file_data; content:"clsid"; nocase; distance:0; created_at 2008_08_03, updated_at 2016_05_01; 我想从文件中提取 sid、msg、created_at 和 updated_at,输出应该如下所示 test1 | 16521 | 2010_07_30 | 2016_07_01 test2 | 23476 | 2010_10_30 | 2013_07_11 test3 | 236487| 2008_08_03 | 2016_05_01 我使用的脚本是 cat $file | grep
  • C#从XML提取数据(C# extracting data from XML)
    问题 我正在尝试从URL中的XML读取天气数据。 XML看起来像这样: <weatherdata> <location>...</location> <credit>...</credit> <links>...</links> <meta>...</meta> <sun rise="2013-05-11T04:49:22" set="2013-05-11T21:39:03"/> <forecast> <text>...</text> <tabular> <time from="2013-05-11T01:00:00" to="2013-05-11T06:00:00" period="0"> <!-- Valid from 2013-05-11T01:00:00 to 2013-05-11T06:00:00 --> <symbol number="2" name="Fair" var="mf/02n.03"/> <precipitation value="0" minvalue="0" maxvalue="0.1"/> <!-- Valid at 2013-05-11T01:00:00 --> <windDirection deg="173.8" code="S" name="South"/> <windSpeed mps="4.2" name="Gentle breeze"/>
  • 如何存储历史数据(How to Store Historical Data [closed])
    问题 关门了。 这个问题需要细节或明确性。 它当前不接受答案。 想要改善这个问题吗? 添加详细信息,并通过编辑此帖子来澄清问题。 5个月前关闭。 改善这个问题 我和一些同事就存储历史数据的最佳方法进行了辩论。 当前,对于某些系统,我使用一个单独的表来存储历史数据,并且为当前的活动记录保留一个原始表。 因此,假设我有表FOO。 在我的系统下,所有活动记录都将进入FOO,所有历史记录都将进入FOO_Hist。 用户可以更新FOO中的许多不同字段,因此我想对更新的所有内容保持准确的描述。 FOO_Hist保留与FOO完全相同的字段,但自动递增的HIST_ID除外。 每次更新FOO时,我都会在FOO_Hist中执行一条插入语句,类似于: insert into FOO_HIST select * from FOO where id = @id 。 我的同事说这是错误的设计,因为出于历史原因,我不应该拥有表的确切副本,而应该在活动表中插入另一条记录,并带有标记以表明它是出于历史目的。 是否有处理历史数据存储的标准? 在我看来,考虑到可能超过一百万条记录,我不想将我的活动记录与所有历史记录都放在同一张表中(我在考虑长期)。 您或您的公司如何处理此问题? 我正在使用MS SQL Server 2008,但我想保留所有DBMS的通用和任意答案。 回答1
  • SQL Server 2008R2 和创建 XML 文档(SQL Server 2008R2 and creating XML document)
    问题 论坛上的第一篇文章,因为我真的很喜欢这个。 以下查询将有效的 XML 文档正确分配给 @xTempXML 变量(xml 类型)。 注:文档长度(换算成varchar(max) = 711 select @xTempXML = ( select PrescriberFirstName as "row/prescriber/name/first", PrescriberLastName as "row/prescriber/name/last", PrescriberAddress1 as "row/prescriber/address/line1", PrescriberAddress2 as "row/prescriber/address/line2", PrescriberCity as "row/prescriber/address/city", PrescriberState as "row/prescriber/address/state", PrescriberZipCode as "row/prescriber/address/zipcode", PatientFirstName as "row/patient/name/first", PatientLastName as "row/patient/name/last", PatientMiddleName as
  • 在 R 中,如何从 XML 文件中提取两个值,循环遍历 5603 个文件并写入表(In R, how to extracting two values from XML file, looping over 5603 files and write to table)
    问题 由于我对 R 比较陌生,因此我正在尝试学习如何从一个 XML 文件中提取两个值,并在我的工作目录中循环 5603 个其他(小,<2kb)XML 文件。 我一直在阅读很多关于“循环”的主题,但发现这很令人困惑——尤其是因为循环 XML 文件似乎与循环其他文件不同,对吗? 我正在使用 XML 结构的在线数据。 对于每个 XML 文件,我想将“ZipCode”和“AwardAmount”写入表格。 运行以下代码,我确实检索了 ZipCode 和 AwardAmount,但仅从第一个文件中检索。 如何编写正确的循环并将其写入表格? xmlfiles=list.files(pattern="*.xml") for (i in 1:length(xmlfiles)){ doc= xmlTreeParse("xmlfiles[i]", useInternal=TRUE) zipcode<-xmlValue(doc[["//ZipCode"]]) amount<-xmlValue(doc[["//AwardAmount"]]) } 有没有人有一些建议? 回答1 这可能对你有用。 我摆脱了for循环并使用sapply 。 xmlfiles <- list.files(pattern = "*.xml") txtfiles <- gsub("xml", "txt", xmlfiles
  • 从子树中提取值(Extracting Values from a Subtree)
    问题 我正在用HXT解析一个 XML 文件,我试图将一些节点提取分解为模块化部分(我一直在使用它作为我的指南)。 不幸的是,一旦我进行了第一级解析,我就无法弄清楚如何应用某些选择器。 import Text.XML.HXT.Core let node tag = multi (hasName tag) xml <- readFile "test.xml" let doc = readString [withValidate yes, withParseHTML no, withWarnings no] xml books <- runX $ doc >>> node "book" 我看到书籍有一个类型[XmlTree] :t books books :: [XmlTree] 现在我想获取books的第一个元素,然后在子树中提取一些值。 let b = head(books) runX $ b >>> node "cost" Couldn't match type ‘Data.Tree.NTree.TypeDefs.NTree’ with ‘IOSLA (XIOState ()) XmlTree’ Expected type: IOSLA (XIOState ()) XmlTree XNode Actual type: XmlTree In the first argument of
  • 从 XML 中提取一小部分数据(Extracting a small subset of data from XMLs)
    问题 我正在编写一个 C#/VB 程序,用于根据 XML 中收到的信息报告数据。 我的情况是我每个月都会收到很多 XML(大约 100-200)——每个 XML 的大小从 10mb 到 350mb 不等。 对于这些 XML 中的每一个,我只需要其数据的一小部分(少于任何一个文件的整个数据的 5%),以便生成必要的报告。 此外,该数据子集将始终保存在相同的键结构中(它可能存在于多个键中并处于不同的级别,也许,但它始终存在于相同的键名中/包含它的键将始终具有具有相同的属性,例如“名称”等) 因此,我目前关于如何执行此操作的想法是: 创建一个“scraper”,使用 XPath 从 XML 中提取必要的数据。 将必要数据的一小部分存储在 SQL Server 表中,并将文件特征数据存储在单独的表中,以便了解这些抓取的数据来自哪个文件将数据查询到程序中进行报告。 我在这里的主要问题真的是刮掉这些数据的最佳方法是什么? 我最熟悉 XPath,但是对于大小为 200MB 的多个文件,我担心在整个文件中加载性能问题。 我见过/研究过的其他事情是: 创建一个 XSLT 文件以仅从 XML 中转换/提取我想要的数据使用 Linq 到 XML 以某种方式将 XML 链接到 SQL 服务器,然后能够直接查询它们使用 ADO 从程序中查询 XML 使用 XMLReader 类进行操作(而不是完全加载到每个
  • SQL Server查询xml属性中的元素值(SQL Server query xml attribute for an element value)
    问题 抱歉,如果还有其他地方,我发现了很多类似的示例,但是我无法使其与我的数据一起使用。 2天后,我需要一个答案:( 基本上有一个SQL Server表,其中的列包含XML数据。 此数据包含我需要提取的值。 这是我的XML。 <CustomFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd"> <Field fieldName="AutoCategory">Event Log</Field> <Field fieldName="SType">Event Log</Field> <Field fieldName="STag1">AgentGuid</Field> <Field fieldName="STag2">AlertRegistrationId</Field> <Field fieldName="STag3">LogType</Field> <Field fieldName="SValue1">619764177412541</Field> <Field fieldName=
  • 如何在sql server中提取xml值字段(mysql中的extractvalue函数)(how to extract xml value field with in sql server ( extractvalue function in mysql ))
    问题 我必须在 sql server 中提取 xml 值。 通常,当我提取存储在 mysql 中的值字段时,我可以简单地使用extractvalue(data,'parent/child/node') as value 谁能告诉我如何在 sql server 中做到这一点? 谢谢 。 回答1 像这样: declare @x xml = '<foo><bar>my value</bar></foo>' select @x.value('(/foo/bar)[1]', 'varchar(30)') 这同样适用于 XML 列。
  • 使用 Oracle pl/sql 从带有命名空间的 xml clob 中提取值(Extracting value from xml clob with Namespace using Oracle pl/sql)
    问题 我目前正在尝试从clob列中提取值,但总是得到结果为null 。 我尝试了许多可能的场景,但对我来说它总是返回null 。 附上实际的xml <TenderOffer xmlns="http://xmlns.oracle.com/apps/otm"> <Shipment> <ShipmentHeader/> <SEquipment/> <ShipmentStop/> <ShipmentStop/> <Location/> <Location/> <Release/> <RATE_OFFERING> <RATE_OFFERING_ROW> <USER_CLASSIFICATION3>ZXF</USER_CLASSIFICATION3> </RATE_OFFERING_ROW> </RATE_OFFERING> </Shipment> </TenderOffer> 下面是实际的查询, select itc.element_name, extractvalue(XMLTYPE(XML_BLOB), '/TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW/USER_CLASSIFICATION3/text()'), XMLTYPE(XML_BLOB) from i_transaction itc where itc.i
  • 从 xml url 中提取值(Extracting the values from a xml url)
    问题 我需要从这个 URL https://clip.unl.pt/sprs?lg=pt&year=2013&uo=97747&srv=rsu&p=1&tp=s&md=3&rs=8145&it=1030123459 打印 Identificador 标签内的值,但我没有得到任何输出全部。 function get_xmlcontent(StdClass $data) { $year = $data->year; $course = $data->clipid; $typeperiod = $data->typeperiod; if ($typeperiod == 's'||$typeperiod == 'a') { $period = $data->period; } else if ($typeperiod == 't') { $period = $data->trimester; } //file from CLIP $xmlUrl = 'https://clip.unl.pt/sprs?lg=pt&year='.$year.'&uo=97747&srv=rsu&p='.$period.'&tp='.$typeperiod.'&md=3&rs='.$course.'&it=1030123459'; $xmlStr = download_file_content($xmlUrl)