sql for xml query sample,xmlsample

來源:互聯網
上載者:User

sql for xml query sample,xmlsample

sample 1:

declare @x xml   select @x='<ArrayOfScheduledTime>  <ScheduledTime>    <RecurrenceType>EveryMonday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T19:30:00</dateTime>    </Stamp>  </ScheduledTime>  <ScheduledTime>    <RecurrenceType>EveryThursday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T19:30:00</dateTime>    </Stamp>  </ScheduledTime>  <ScheduledTime>    <RecurrenceType>EverySunday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T19:30:00</dateTime>    </Stamp>  </ScheduledTime></ArrayOfScheduledTime>'SELECT N.v.value('.' , 'VARCHAR(100)')B FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')N(v)/*EveryMondayEveryThursdayEverySunday*/

select @x.query('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')/*<RecurrenceType>EveryMonday</RecurrenceType><RecurrenceType>EveryThursday</RecurrenceType><RecurrenceType>EverySunday</RecurrenceType>*/

select o.value('RecurrenceType[1]','varchar(20)') 'RecurrenceType' from (select @x 'x') t cross apply x.nodes('/ArrayOfScheduledTime/ScheduledTime') x(o) /*RecurrenceType--------------------EveryMondayEveryThursdayEverySunday (3 行受影響)*/

DECLARE @handel int;EXEC sp_xml_preparedocument @handel output, @xSELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime',2)WITH(RecurrenceType varchar(50)) /**************結果*****************EveryMondayEveryThursdayEverySunday*/

sample2:

DECLARE @xDailyConfig XMLset @xDailyConfig='<ArrayOfScheduledTime>  <ScheduledTime>    <RecurrenceType>Everyday</RecurrenceType>    <Stamp>      <dateTime>2000-01-01T09:00:00</dateTime>      <dateTime>2000-01-01T13:00:00</dateTime>      <dateTime>2000-01-01T19:00:00</dateTime>    </Stamp>  </ScheduledTime></ArrayOfScheduledTime>'SELECT N.v.value('.' , 'VARCHAR(100)')B FROM @xDailyConfig.nodes('/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime')N(v)/*2000-01-01T09:00:002000-01-01T13:00:002000-01-01T19:00:00*/



用sql的for xml 語句從資料庫產生的臨時xml

js使用這個來解析xml:
var xmlDoc = new ActiveXObject('MSXML2.DOMDocument');
xmlDoc.async = false;
xmlDoc.loadXML(xml);
var xmlRoot = xmlDoc.documentElement;
//取元素對象
var Nodes = xmlRoot.getElementsByTagName("Nodes").item(0);
//遍曆
for ( var i = 0;i < Nodes.childNodes.length;i++ ){
Node = Nodes.childNodes.item(i);
//取值
Node.getElementsByTagName('BaseProperties').item(0).getAttribute('id');

xml形如:<Nodes><Node><BaseProperties id="N1234"...
 
sql for xml!!!!!!!!!!!!!!!

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.