Student Academic blog

DECLARE @FilePath VARCHAR(100)
DECLARE @xml XML
SELECT @FilePath = 'C:\a.xml'
 
--Stuff xml from flat file into a form that sql can work with
SELECT @xml = BulkColumn FROM OPENROWSET
(BULK 'C:\a.xml', SINGLE_BLOB) AS CustomerXML
 
--Insert xml data into worker table
--INSERT INTO #ImportCustomers
SELECT
x.value('to[1]', 'VARCHAR(100)'),
x.value('from[1]', 'VARCHAR(100)')
FROM @xml.nodes('/notes/note') notes(x)

DECLARE @xml xml
SET @xml = N'<polist>
               <po ponumber="100" podate="2008-09-10" />
               <po ponumber="101" podate="2008-09-11" />
             </polist>'
SELECT
 doc.col.value('@ponumber', 'nvarchar(10)') ponumber
,doc.col.value('@podate', 'datetime') podate
FROM @xml.nodes('/polist/po') doc(col)

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)

SET @XmlDocument = N'<polist>
<po><ponumber>100</ponumber><podate>2008-09-10</podate></po>
<po><ponumber>101</ponumber><podate>2008-09-11</podate></po>
</polist>'

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

SELECT * FROM OPENXML (@DocHandle, '/polist/po',2)
WITH (ponumber nvarchar(10),
podate datetime)

EXEC sp_xml_removedocument @DocHandle

posted by Aamir Hasan

Student Acdemic Blog

ALL, SQL 2005 & 2008