XML檔案到SQLServer查詢

如何從 XML 檔案獲取資料到 SQL 查詢，以下代碼不起作用。

<FVDL>
        <EngineData>
           <RuleInfo>
             <Rule id="13EFF385-69A9-494A-9C67-951FEDAB25ED">
               <MetaInfo>
                 <Group name="package">Python Core xml</Group>
                 <Group name="inputsource">XML Document</Group>
                 <Group name="audience">broad</Group>
               </MetaInfo>
             </Rule>
            <Rule id="E9DB1C0E-025E-4EBF-A804-6C3DA413E652">
               <MetaInfo>
                 <Group name="altcategoryMIS">Python Core zipfile</Group>
                 <Group name="altcategoryGDPR">Access Violation</Group>
               </MetaInfo>
             </Rule>  
           </RuleInfo>
        </EngineData>
    </FVDL>
             
    
    USE OPENXMLTesting
    GO
    
    DECLARE @XML AS XML, @hDoc AS INT
    
    SELECT @XML = XMLData FROM XMLwithOpenXML
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
    
    Select ID, name, [Group]
    FROM OPENXML(@hDoc, 'FVDL/EngineData/RuleInfo') 
    
    WITH  
    (
    ID [varchar](100) 'Rule/@id',
    [name] [varchar](100) 'Rule/MetaInfo/Group/@name',
    [Group] [varchar](1000) 'MetaInfo/Group/.. '
    )  
    
    EXEC sp_xml_removedocument @hDoc

尋找這樣的結果

uj5u.com熱心網友回復：

請嘗試以下解決方案。

從 SQL Server 2005 開始，在處理 XML 資料型別時，最好使用基于 w3c 標準的 XQuery 語言。

Microsoft 專有OPENXML和它的伙伴sp_xml_preparedocument，sp_xml_removedocument保留它只是為了與過時的 SQL Server 2000 向后兼容。它們的使用減少到很少的邊緣情況。強烈建議重新撰寫您的 SQL 并將其切換到 XQuery。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<FVDL>
    <EngineData>
        <RuleInfo>
            <Rule id="13EFF385-69A9-494A-9C67-951FEDAB25ED">
                <MetaInfo>
                    <Group name="package">Python Core xml</Group>
                    <Group name="inputsource">XML Document</Group>
                    <Group name="audience">broad</Group>
                </MetaInfo>
            </Rule>
            <Rule id="E9DB1C0E-025E-4EBF-A804-6C3DA413E652">
                <MetaInfo>
                    <Group name="altcategoryMIS">Python Core zipfile</Group>
                    <Group name="altcategoryGDPR">Access Violation</Group>
                </MetaInfo>
            </Rule>
        </RuleInfo>
    </EngineData>
</FVDL>');
-- DDL and sample data population, end

SELECT p.value('@id', 'UNIQUEIDENTIFIER') AS ID
    , c.value('@name', 'VARCHAR(30)')AS [Name]
    , c.value('(./text())[1]', 'VARCHAR(30)')AS [Group]
FROM @tbl
    CROSS APPLY xmldata.nodes('/FVDL/EngineData/RuleInfo/Rule') AS t1(p)
    CROSS APPLY t1.p.nodes('MetaInfo/Group') AS t2(c);

輸出

 -------------------------------- ----------------- --------------------- 
|               ID               |      Name       |       Group         |
 -------------------------------- ----------------- --------------------- 
| 13EFF385-69A9-494A-9C67-951FED | package         | Python Core xml     |
| 13EFF385-69A9-494A-9C67-951FED | inputsource     | XML Document        |
| 13EFF385-69A9-494A-9C67-951FED | audience        | broad               |
| E9DB1C0E-025E-4EBF-A804-6C3DA4 | altcategoryMIS  | Python Core zipfile |
| E9DB1C0E-025E-4EBF-A804-6C3DA4 | altcategoryGDPR | Access Violation    |
 -------------------------------- ----------------- --------------------- 

uj5u.com熱心網友回復：

OPENXML 有點不穩定并且已經過時了。如果您需要使用它，您需要提供向下到<Group>其他元素的路徑，然后再回傳到其他元素。這應該根據您的示例 XML 為您完成。

DECLARE @XML XML = N'<FVDL>
        <EngineData>
           <RuleInfo>
             <Rule id="13EFF385-69A9-494A-9C67-951FEDAB25ED">
               <MetaInfo>
                 <Group name="package">Python Core xml</Group>
                 <Group name="inputsource">XML Document</Group>
                 <Group name="audience">broad</Group>
               </MetaInfo>
             </Rule>
            <Rule id="E9DB1C0E-025E-4EBF-A804-6C3DA413E652">
               <MetaInfo>
                 <Group name="altcategoryMIS">Python Core zipfile</Group>
                 <Group name="altcategoryGDPR">Access Violation</Group>
               </MetaInfo>
             </Rule>  
           </RuleInfo>
        </EngineData>
    </FVDL>'
    
DECLARE @hDoc AS INT
    
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
    
Select ID, name, [Group]
FROM OPENXML(@hDoc, 'FVDL/EngineData/RuleInfo/Rule/MetaInfo/Group') 
WITH  
(
    id [varchar](100) '../../@id',
    [name] [varchar](100) '@name',
    [Group] [varchar](1000) '.'
)  
    
EXEC sp_xml_removedocument @hDoc

uj5u.com熱心網友回復：

我強烈建議您不要使用OPENXML，因為它有很多問題。

而是使用較新的 XQuery 函式，它們使用起來要簡單得多

• 注意一個如何.nodes進入下一個。這只是必要的，因為有兩個單獨的節點級別需要分成單獨的行。

SELECT
    ID = rl.value('@id','uniqueidentifier'),
    [name] = grp.value('@name', 'varchar(100)'),
    [Group] = grp.value('text()[1]','varchar(1000)')
FROM XMLwithOpenXML
CROSS APPLY XMLData.nodes('FVDL/EngineData/RuleInfo/Rule') x1(rl)
CROSS APPLY x1.rl.nodes('MetaInfo/Group') x2(Grp);

資料庫<>小提琴

標籤：sql sql-server xml 打开xml

上一篇：熊貓|讀_csv|to_xml|ValueError:無效的標簽名稱'foobar'

下一篇：使用C#在XML檔案中轉換CSV