使用 Xquery 检索 xml 属性

问题描述

我使用以下查询从 XML 文件的属性 ad 元素中选择 XML 的值，但我无法从 XML 页面读取 seq、id、reported dated 属性所以任何人请建议如何使用此查询获取属性值.

I am using the below query to select the values of XML from attributes ad elements of the XML file but I am not able to read the seq, id, reported dated attributes from XML page so any one please suggest How to get values of attributes using this Query.

select a_node.value('(./text())[1]', 'var char(50)') AS c_val,
c1_node.value('(./text())[1]', 'var char(50)') AS c_val 2,
ca_node.value('(./text())[1]', 'var char(50)') AS c_val3, 
d_node.value('(./text())[1]', 'var char(50)') ,
e_node.value('(./text())[1]', 'varchar(50)') ,
f_node.value('(./text())[1]', 'var char(50)') 
FROM @xmlData.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') AS b(b_node) 
outer APPLY b.b_node.nodes('./x:primarykey') AS pK_InquiryResponse (a_node) 
outer APPLY b.b_node.nodes('./x:seq') AS CustomerCode (c1_node) 
outer APPLY b.b_node.nodes('./x:id') AS amount (ca_node)
outer APPLY b.b_node.nodes('./x:ReportedDate') AS CustRefField (d_node)
outer APPLY b.b_node.nodes('./x:AccountNumber') AS ReportOrderNO (e_node)
outer apply b.b_node.nodes('./x:CurrentBalance') as additional_id (f_node);

编辑:评论中提供的 Xml 片段

Edit: Xml Snippets Provided in Comments

<sch:Account seq="2" id="345778174" ReportedDate="2014-01-01">
    <sch:AccountNumber>TSTC1595</sch:AccountNumber>
    <sch:CurrentBalance>0</sch:CurrentBalance>
    <sch:Institution>Muthoot Fincorp Limited</sch:Institution>
    <sch:PastDueAmount>0</sch:PastDueAmount>
    <sch:DisbursedAmount>12000</sch:DisbursedAmount>
    <sch:LoanCategory>JOG Group</sch:LoanCategory>
</sch:Account>

<sch:Account seq="2" id="345778174" ReportedDate="2014-01-01">
    <sch:BranchIDMFI>THRISSUR ROAD</sch:BranchIDMFI>
    <sch:KendraIDMFI>COSTCO/RECENT-107</sch:KendraIDMFI>
</sch:Account>

推荐答案

使用 Xml Loose @Variable 解析 XQuery

假设有一个与此类似的 Xml 文档(即具有一个元素上的所有属性):

Assuming an Xml document similar to this (viz with all the attributes on one element):

DECLARE @xmlData XML = 
N'<Reports xmlns:x="http://foo">
    <x:InquiryResponse>
        <x:ReportData>
            <x:AccountDetails>
                <x:Account x:primarykey="pk" x:seq="sq" x:id="id"
                             x:ReportedDate="2014-01-01T00:00:00" />
            </x:AccountDetails>
        </x:ReportData>
    </x:InquiryResponse>
</Reports>';

您可以按如下方式抓取属性:

You can scrape the attributes out as follows:

WITH XMLNAMESPACES('http://foo' AS x)
select 
    Nodes.node.value('(@x:primarykey)[1]', 'varchar(50)') AS c_val,
    Nodes.node.value('(@x:seq)[1]', 'varchar(50)') AS c_val2,
    Nodes.node.value('(@x:id)[1]', 'varchar(50)') AS c_val3, 
    Nodes.node.value('(@x:ReportedDate)[1]', 'DATETIME') as someDateTime
FROM 
   @xmlData.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') 
   AS Nodes(node);

• 属性不需要 text() 因为它们自动是字符串
• 在命名空间中具有属性是相当不寻常的 - 如果没有，请删除 xmlns 别名前缀.
• Attributes don't need text() as they are automatically strings
• It is fairly unusual to have attributes in a namespace - drop the xmlns alias prefix if they aren't.

此处为 SqlFiddle

编辑 - 解析 Xml 列

• 从属性中删除的命名空间- 假设你有一个表中的数据，而不是一个变量，因此 APPLY 要求.请注意， OUTER APPLY 将返回空值，例如仅当您有行时有用空 Xml 或缺少 Xml 元素.CROSS APPLY 是常态(即将 xpath 应用于 LHS 表上选定的每一行)
• 元素的访问方式类似于属性，只是没有@
• Namespace dropped from the attributes -Assumed that you have the data in a table, not a variable, hence the APPLY requirement. Note that OUTER APPLY will return nulls, e.g. useful only if you have rows with empty Xml or missing Xml Elements. CROSS APPLY is the norm (viz applying the xpath to each row selected on the LHS table)
• Elements are accessed similar to attributes, just without @




WITH XMLNAMESPACES('http://foo' AS x)
select 
    Nodes.node.value('(@seq)[1]', 'varchar(50)') AS c_val2,
    Nodes.node.value('(@id)[1]', 'varchar(50)') AS c_val3, 
    Nodes.node.value('(@ReportedDate)[1]', 'DATETIME') as someDateTime,
    Nodes.node.value('(x:AccountNumber)[1]', 'VARCHAR(50)') as accountNumber
FROM 
    MyXmlData z
    CROSS APPLY
z.XmlColumn.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') 
      AS Nodes(node);

更新小提琴

从磁盘编辑 Xml 文件

这是从磁盘读取的 xml 文件的相同内容.请注意，一旦您在 XML 变量 (@MyXmlData) 中拥有数据，您就不需要 CROSS APPLY 到任何东西 - 只需提供xpath 选择合适的节点，然后刮取元素和属性.

Here's the same thing for an xml file read from disk. Note that once you have the data in an XML variable (@MyXmlData) that you don't need to CROSS APPLY to anything - just supply xpath to select the appropriate node, and then scrape out the elements and attributes.

DECLARE @MyXmlData XML;
SET @MyXmlData = 
( SELECT * FROM OPENROWSET ( BULK N'c:\temp\file3098.xml', SINGLE_CLOB ) AS MyXmlData );
-- Assuming all on the one element, no need for all the applies
-- attributes don't have a text axis (they are automatically strings

WITH XMLNAMESPACES('http://foo' AS x)
select 
    Nodes.node.value('(@seq)[1]', 'varchar(50)') AS c_val2,
    Nodes.node.value('(@id)[1]', 'varchar(50)') AS c_val3, 
    Nodes.node.value('(@ReportedDate)[1]', 'DATETIME') as someDateTime,
    Nodes.node.value('(x:AccountNumber)[1]', 'VARCHAR(50)') as accountNumber
FROM 
      @MyXmlData.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') 
      AS Nodes(node);

这篇关于使用 Xquery 检索 xml 属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！