asked on

Reading XML from Database

The northwind sample database 2008R2 has resumes stored in XML format. Does anyone have an example of how to read and save xml data from and to?

I want to build a resume editing tool.

Kumaraswamy R

Hi

try

--Read from XML Data using OpenXML
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

--Read from XML Data using code snippet
DECLARE @xml xml
SET @xml = N'<polist>
<po><ponumber>100</ponumber><podate>2008-09-10</podate></po>
<po><ponumber>101</ponumber><podate>2008-09-11</podate></po>
</polist>'
SELECT
doc.col.value('ponumber[1]', 'nvarchar(10)') ponumber
,doc.col.value('podate[1]', 'datetime') podate
FROM @xml.nodes('/polist/po') doc(col)

--Read from XML Data using attributes instead of elements:

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

ASKER CERTIFIED SOLUTION

Kumaraswamy R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Annette Wilson, MSIS

ASKER

Thank you.