SQL SERVER:如何从 XML 文件进行查询

SQL SERVER : How to make query from XML file(SQL SERVER:如何从 XML 文件进行查询)
本文介绍了SQL SERVER:如何从 XML 文件进行查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有两个这样的 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 文件进行查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)