問題描述

我使用以下查詢從 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 屬性的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！