sql server openxml tips

use openxml to operate the xml document with namespace

-- example one
declare @xml nvarchar(1000)
SET @xml = '
test1
test2
'
DECLARE @Doc int
EXEC sp_xml_preparedocument @Doc OUTPUT, @xml, ''
select *
FROM OpenXml(@Doc, '/ whxbb:head/ whxbb:test',2)
with( d nvarchar(50) 'text()')
EXEC sp_xml_removedocument @Doc


-- example two
declare @xml nvarchar(1000)
SET @xml = '
test1
test2
'
DECLARE @Doc int
EXEC sp_xml_preparedocument @Doc OUTPUT, @xml, ''
select *
FROM OpenXml(@Doc, '/ whxbb:head/ whxbb:test',2)
with( d nvarchar(50) 'text()')
EXEC sp_xml_removedocument @Doc

 

fetch the node value in the same parent with same name

declare @xml nvarchar(1000)
SET @xml = '
test1
test2
'
DECLARE @Doc int
EXEC sp_xml_preparedocument @Doc OUTPUT, @xml
select *
FROM OpenXml(@Doc, '/head/test', 1)
with( comment nvarchar(300) 'text()' )
EXEC sp_xml_removedocument @Doc

 

Update and Insert operate with openxml

-- Insert 
INSERT INTO fnd_tabs
SELECT *
FROM OPENXML(@doc, '/NewDataSet/Table1', 2)
WITH fnd_tabs

 

-- Update 
UPDATE fnd_modules
SET
fnd_modules.module_name = xml_module_name,
fnd_modules.module_user_name = xml_module_user_name,
fnd_modules.module_src = xml_module_src,
fnd_modules.module_type = xml_module_type,
fnd_modules.module_catalog = xml_module_catalog,
fnd_modules.description = xml_description,
fnd_modules.is_valid = xml_is_valid
FROM OPENXML(@doc, '/Modules/Module', 3)
WITH (
xml_module_id int '@module_id',
xml_module_name nvarchar(200) 'module_name',
xml_module_user_name nvarchar(400) 'module_user_name',
xml_module_src nvarchar(200) 'module_src',
xml_module_type nvarchar(200) 'module_type',
xml_module_catalog int 'module_catalog',
xml_is_valid bit 'is_valid',
xml_description nvarchar(480) 'description'
)
WHERE fnd_modules.module_id = xml_module_id