SQLXml 處理帶namespace的xml

查閱了MSDN,其實很簡單

sp_xml_preparedocumenthdoc OUTPUT[ , xmltext ][ , xpath_namespaces ] 

You can specify ns using the third parameter.

declare @MailContent as nvarchar(max)
select @MailContent=MessageBody from MailStorer where ID=2

--print @MailContent

DECLARE @XmlHandler int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlHandler OUTPUT, @MailContent,'<ns0:RN_4C1_OUT xmlns:ns0="http://_4C1_InventoryHold_Intel.MailMessage"/>'

SELECT  '4C1 Inventory Hold On/Off' as Header,*
FROM       OPENXML (@XmlHandler, '/ns0:RN_4C1_OUT/InventoryLine',1)
with
(
    Status nvarchar(50) 'Status',
    Date nvarchar(50) 'DocDate',
    IDocNo nvarchar(50) 'IDocNo',
    SKU nvarchar(50) 'SKU',
    Quantity nvarchar(50) 'Qty',
    RequestBy nvarchar(50) 'RequestBy'
)

-- 清除xml檔案控制代碼，釋放記憶體
exec sp_xml_removedocument @XmlHandler