sql 讀取xml

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type

SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>';

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.

SELECT * FROM OPENXML(@docHandle, N'/Request/DataSet/dsForm/Form') WITH (UserID nchar(10), ApplyDate datetime);
EXEC sp_xml_removedocument @docHandle;

 

 

DECLARE @idoc int
DECLARE @doc varchar(max),@doc2 varchar(max)
select @doc2='<Request><Adapter><Application>CanLearn</Application><Version>5.x</Version><AuthenticationParser>CET</AuthenticationParser><AuthenticationCode>ABCDE-FGHIJ-KLMNO-PQRST-UVWXY_CET</AuthenticationCode></Adapter><Connection><ConnectionGUID>66B8C2E7-F523-48DF-8070-4771294B9054</ConnectionGUID><Sender><SenderName>tpeelap</SenderName><SenderIP>10.110.15.43</SenderIP></Sender></Connection><CommandSet><OperationGUID>E92483FC-30CB-4880-956A-CABA12A86B09</OperationGUID><SiteName>EF2KWeb</SiteName><ActionMode>createForm</ActionMode><!--- KeySet 提供資料識別鍵值--><KeySet><FormID>STDCLC</FormID><UserID>0402435</UserID><ParserRoleID>0402435</ParserRoleID><Subject>常宏梅申請外訓：「E-Form測試三(沒有附件)」2005/10/01~2005/10/10(15小時)</Subject><Important/></KeySet><ConditionSet><Cost>1000</Cost><Nation>1</Nation><!--國別代號代表意義【】台灣【】大陸【】其他 --><DeptID>GPAA310</DeptID><Factory>G</Factory><EmpDeptCode></EmpDeptCode><JobLevelID>7.1</JobLevelID><OrgLevelCode>D070</OrgLevelCode></ConditionSet><Attachments/></CommandSet><!-- Optional 資料描述格式--><DataSchema></DataSchema><!-- Optional 資料集--><DataSet><dsForm xmlns="http://www.tempuri.org/dsForm.xsd">     <Form>      <CanLearnFormID>3FD3A7BD-2BB2-4851-9052-CE7A69375A11</CanLearnFormID>      <ApplyType>2</ApplyType>      <ApplyDate>2005/09/30</ApplyDate>      <UserID>0402435</UserID>      <CourseName>E-Form測試三(沒有附件)</CourseName>      <Description>E-Form測試三(沒有附件)</Description>      <Organization>cet</Organization>      <StartDate>2005/10/01</StartDate>      <EndDate>2005/10/10</EndDate>      <Time>09:30-11:00</Time>      <Hours>15</Hours>      <UserCount>1</UserCount>      <CostPerUser>1000</CostPerUser>      <Money>CNY</Money>      <Cost>1000</Cost>      <CostLocal>1000</CostLocal>      <ReportType>課程大綱,報名表</ReportType>      <Nation>大陸</Nation>      <ApplyReason>E-Form測試三(沒有附件)</ApplyReason>      <Org_URL>cetwww</Org_URL>      <ApplySet>       <Employee>        <UserID>0402435</UserID>       </Employee>      </ApplySet>     </Form>    </dsForm></DataSet></Request>'
 select  @doc=N'<?xml version="1.0" encoding="big5"?>' + replace(convert(nvarchar(max),@doc2),' xmlns="http://www.tempuri.org/dsForm.xsd"','') --from efclca where efclca002='1209240001'
 EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
select * from
openxml (@idoc,'/Request/DataSet/dsForm/Form',2)
with (CanLearnFormID varchar(1000) );
exec sp_xml_removedocument @idoc