问题描述
我有两个这样的 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 小提琴