T-SQL：xml如何使用内连接添加默认命名空间和根路径

Objective: using SQL Server 2012, simplify the xml retrieval as much as possible. As starting point, specify default root path as well as default namespace.

目标:使用SQL Server 2012,尽可能简化xml检索。作为起点,指定默认根路径以及默认命名空间。

Details:

the two tables are quite large, so using the indexes for the join is required. I've been unable to find an example that covers this scenario, although I've seen something similar when building an xml table using FOR XML PATH, type, Root=

这两个表非常大,因此需要使用索引进行连接。我一直无法找到涵盖这种情况的示例,虽然我在使用FOR XML PATH构建xml表时看到类似的东西,类型,Root =

SqlFiddle here: http://sqlfiddle.com/#!6/68df2/2/0

SqlFiddle:http://sqlfiddle.com/#!6/68df2 / 2/0

Create Table t1 (id int, MoreFields varchar(10), DateAdded datetime)
create Table t2 (id int, data xml)

--objective: Using the sql select structure below, make the default root path= /Doc/DocumentProperties

insert into t1 
values (1, 'other data', '2015-10-30 19:30:21.953'),
       (2, 'more data', '2015-10-30 19:30:21.953') 
insert into t2 
values (1,'<Doc xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.MyUrl.com" documentType="customDocument">
  <DocumentProperties>
    <DocumentID>1</DocumentID>
    <Title>blah blah</Title>
    <DateAdded>2015-10-30T15:30:21.9538615-04:00</DateAdded>
    <VisitID>123456</VisitID>
    <Patient>
      <ID>9876</ID>
      <FirstName>john</FirstName>
      <LastName>doe</LastName>
      <MiddleName />
    </Patient>
  </DocumentProperties>
</Doc>')            

;with XMLNamespaces (DEFAULT 'http://www.MyUrl.com')           
select 
    t1.id
    ,t1.DateAdded
    ,xmlDateAdded=t2.data.value('(/Doc/DocumentProperties/DateAdded)[1]', 'datetime') 
    ,Objective='Make /Doc/DocumentProperties the default root path, simplify node value retrieval'
    --since everything is under DocumentProperties I want to do something like: 
    --,xmlDateAdded2    =t2.DateAdded   or
    --,xmlDateAdded3    =?.value('/DateAdded','datetime')

    --will be retrieving other node values as well.
from t1 
inner join t2 
    on t1.id=t2.id 
where t1.id=1--in real world criteria is multiple rows

drop table t1
drop table t2

1 个解决方案

#1

If I understand the question correctly, you can do CROSS APPLY on /Doc/DocumentProperties to make it the context of the column XPath expressions, for example :

如果我正确理解了这个问题,你可以对/ Doc / DocumentProperties进行CROSS APPLY,使其成为列XPath表达式的上下文,例如:

;with XMLNamespaces (DEFAULT 'http://www.MyUrl.com')           
select 
    t1.id
    ,t1.DateAdded
    ,xmlDateAdded=d.value('DateAdded[1]', 'datetime') 
from t1 
inner join t2 on t1.id=t2.id 
cross apply t2.data.nodes('/Doc/DocumentProperties') as x(d)
where t1.id=1

Sqlfiddle Demo

Notice that in the above example you can simply do DateAdded[1] in the column XPath instead of the much longer (/Doc/DocumentProperties/DateAdded)[1].

请注意,在上面的示例中,您只需在XPath列中执行DateAdded [1]而不是更长的时间(/ Doc / DocumentProperties / DateAdded)[1]。

#1