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

2021-09-10 00:00:00 xml tsql sql-server

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

相关文章