-
Notifications
You must be signed in to change notification settings - Fork 0
/
XML-with-Namespaces.sql
69 lines (61 loc) · 2.25 KB
/
XML-with-Namespaces.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
declare @xmlWithNs nvarchar(max) = N'
<addressBook xmlns="http://www.tempuri.org/addressBook.xsd">
<entries xmlns:e="http://www.tempuri.org/addressBookProperties.xsd">
<entry e:name="Siddharth Barman" e:email="siddharthbarman@email.com" />
<entry e:name="Sajan Kumar" e:email="sajankumar@email.com" />
</entries>
</addressBook>';
declare @xmlWithoutNs nvarchar(max) = N'
<addressBook>
<entries>
<entry name="Siddharth Barman" email="siddharthbarman@email.com" />
<entry name="Sajan Kumar" email="sajankumar@email.com" />
</entries>
</addressBook>';
-- Parsing XML without namespaces
declare @xml nvarchar(max) = @xmlWithoutNs;
declare @idoc int
exec sp_xml_preparedocument @idoc output, @xml;
select * from OPENXML(@idoc, N'/addressBook/entries/entry')
with
(
[name] varchar(50),
[email] varchar(50)
);
exec sp_xml_removedocument @idoc;
-- Reading XML with namespaces incorrectly
set @xml = @xmlWithNs;
exec sp_xml_preparedocument @idoc output, @xml
select * from OPENXML(@idoc, N'/addressBook/entries/entry')
with
(
[name] varchar(50),
[email] varchar(50)
)
exec sp_xml_removedocument @idoc;
-- Reading XML with namespaces correctly
exec sp_xml_preparedocument @idoc output, @xml, N'<root xmlns:d="http://www.tempuri.org/addressBook.xsd" xmlns:e="http://www.tempuri.org/addressBookProperties.xsd"/>' ;
select * from OPENXML(@idoc, N'/d:addressBook/d:entries/d:entry')
with
(
[e:name] varchar(50),
[e:email] varchar(50)
);
exec sp_xml_removedocument @idoc;
-- Reading XML with namespaces correctly but renaming the columns and reading the node text
set @xmlWithNs = N'
<addressBook xmlns="http://www.tempuri.org/addressBook.xsd">
<entries xmlns:e="http://www.tempuri.org/addressBookProperties.xsd">
<entry e:id="1" e:email="siddharthbarman@email.com">Siddharth Barman</entry>
<entry e:id="2" e:email="sajankumar@email.com">Sajan Kumar</entry>
</entries>
</addressBook>';
exec sp_xml_preparedocument @idoc output, @xmlWithNs, N'<root xmlns:d="http://www.tempuri.org/addressBook.xsd" xmlns:e="http://www.tempuri.org/addressBookProperties.xsd"/>' ;
select * from OPENXML(@idoc, N'/d:addressBook/d:entries/d:entry')
with
(
ID int '@e:id',
FullName varchar(50) 'text()',
EmailID varchar(50) '@e:email'
);
exec sp_xml_removedocument @idoc;