問題描述
我有兩個這樣的 xml 文件:
I have two xml file like this:
<?xml version="1.0"?>
<instance xmlns="http://www.ubrea.com/xforms/88668970-6edb-0131-28e9-22000a1cda92" xmlns:tm="http://www.ubrea.com/xforms" >
<inputs>
<Truck_Number_Non_Barcode>MNKSJJHDHH88728</Truck_Number_Non_Barcode>
<VIN>
<Non_Barcode>xyz</Non_Barcode>
<ODO>1425788</ODO>
<Defect>
<Code>33J</Code>
</Defect>
</VIN>
</inputs>
</instance>
還有
<?xml version="1.0"?>
<instance xmlns="http://www.ubrea.com/xforms/88668970-6edb-0131-28e9-22000a1cda92" xmlns:tm="http://www.ubrea.com/xforms" >
<inputs>
<Number_Non_Barcode>mnbcdsddsd3455</Number_Non_Barcode>
<VIN>
<Non_Barcode>xyz</Non_Barcode>
<ODO>1425788</ODO>
</VIN>
</inputs>
</instance>
下面是我解析xml文件的sqlquery:
Below is my sqlquery for parsing xml file:
declare @xmldata xml
set @xmldata ='
<?xml version="1.0"?>
<instance xmlns="http://www.ubrea.com/xforms/88668970-6edb-0131-28e9-22000a1cda92" xmlns:tm="http://www.ubrea.com/xforms" >
<inputs>
<Number_Non_Barcode>mnbcdsddsd3455</Number_Non_Barcode>
<VIN>
<Non_Barcode>xyz</Non_Barcode>
<ODO>1425788</ODO>
</VIN>
</inputs>
</instance>'
declare @sql nvarchar(max)
declare @xmlns varchar(max)
set @xmlns=''''+SUBSTRING(cast(@xmldata as varchar(max)),CHARINDEX('http://www.ubrea.com/xforms/',cast(@xmldata as varchar(max)),1),CHARINDEX('" xmlns:dm',cast(@xmldata as varchar(max)),1)-18)+''''
set @sql='
declare @xmldata xml
set @xmldata = '''+cast(@xmldata as varchar(max))+'''
begin try
;WITH XMLNAMESPACES
(
DEFAULT ' + @xmlns +
','+'''http://www.ubrea.com/xforms''' + ' as fm
)
select
Number_Non_Barcode, Non_Barcode, ODO, Code
from (
select
x.c.value(''(../../Number_Non_Barcode)[1]'', ''varchar(100)'') as Number_Non_Barcode,
x.c.value(''(../Non_Barcode)[1]'', ''varchar(100)'') as Non_Barcode,
x.c.value(''(../ODO)[1]'', ''varchar(100)'') as ODO,
x.c.value(''(Code)[1]'', ''varchar(100)'') as Code
from @xmldata.nodes(''/instance/inputs/VIN/Defect'') x(c)
) x
end try
begin catch
select ERROR_NUMBER() Code, ERROR_MESSAGE() Message
end catch'
exec sp_executesql @sql;
如果
不可用,如何進行查詢,結果應該是這樣的:
How to make Query if <Defect>
not available and the result should like this:
Number_Non_Barcode Non_Barcode ODO Defect_Code
mnbcdsddsd3455 xyz 1425788 NULL
我對 xml 查詢不太熟悉,似乎找不到如何執行此操作的示例.任何幫助,將不勝感激.謝謝
I'm not real familiar with xml query and can't seem to find an example of how to do it. Any help would be appreciated. Thanks
推薦答案
我猜你的動態查詢在那里是因為你不知道默認的命名空間.相反,您可以使用 *
在 XQuery 中指定名稱空間.
I guess that your dynamic query is there because the default namespace is unknown to you. Instead you can specify the namespace in the XQuery using *
.
使用父軸 ..
可以通過在幾個步驟中使用 nodes()
和 cross apply
分解 XML 來避免.由于 Defect
節點可能會丟失,因此在粉碎這些節點時必須使用 outer apply
.
Using the parent axis ..
can be avoided by shredding the XML using nodes()
and cross apply
in several steps. Since the Defect
node can be missing you have to use outer apply
when shredding those nodes.
select I.X.value('(*:Number_Non_Barcode/text())[1]', 'varchar(100)') as Number_Non_Barcode,
V.X.value('(*:Non_Barcode/text())[1]', 'varchar(100)') as Non_Barcode,
V.X.value('(*:ODO/text())[1]', 'varchar(100)') as ODO,
D.X.value('(*:Code/text())[1]', 'varchar(100)') as Code
from @XMLData.nodes('/*:instance/*:inputs') as I(X)
cross apply I.X.nodes('*:VIN') as V(X)
outer apply V.X.nodes('*:Defect') as D(X)
SQL 小提琴
這篇關于SQL SERVER:如何從 XML 文件進行查詢的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!