如果只有单个节点没有SQL中的父节点，那么如何读取XML [英] How to read XML, if have only single node without parent nodes in SQL

问题描述

大家好，



在我的sql表中我有一列xml数据类型。



在这个col我有一个没有父节点的xml，我的意思是，我在这个xml中只有一个节点。

hi guys,

in my sql table i have one column of xml datatype.

In this col i have an xml without parent node, i mean, i have only one single node in this xml.

<FieldTicket FID="3113" TC="1" JobName="Edited:" PriceRef="2245" OurRef="2245" Qty="4.00" Price="118.00" SubTotal="0.00" Discount="0" />

如何在SQL中读取此xml n从中获取数据。



请帮帮我。



谢谢



我尝试过：



如果有父节点：

how can i read this xml n fetch data from it, in SQL.

please help me.

Thanks

What I have tried:

if have parent node:

SELECT CustomerID, CustomerName, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer')
WITH 
(
CustomerID [varchar](50) '@CustomerID',
CustomerName [varchar](100) '@CustomerName',
Address [varchar](100) 'Address'
)

推荐答案

尝试这样的事情：

Try something like this:

SELECT
    X.Ticket.value('@FID', 'int') As FID,
    X.Ticket.value('@TC', 'int') As TC,
    X.Ticket.value('@JobName', 'varchar(50)') As JobName,
    X.Ticket.value('@PriceRef', 'int') As PriceRef,
    X.Ticket.value('@OurRef', 'int') As OurRef,
    X.Ticket.value('@Qty', 'float') As Qty,
    X.Ticket.value('@Price', 'money') As Price,
    X.Ticket.value('@SubTotal', 'money') As SubTotal,
    X.Ticket.value('@Discount', 'numeric') As Discount
FROM
    YourTable As T
    CROSS APPLY T.YourXmlColumn.nodes('//FieldTicket') As X (Ticket)
;

这篇关于如果只有单个节点没有SQL中的父节点，那么如何读取XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！