sql for xml query sample，xmlsample

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)

/*
EveryMonday
EveryThursday
EverySunday
*/

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
--------------------
EveryMonday
EveryThursday
EverySunday
 
(3 行受影响)
*/

DECLARE @handel int;
EXEC sp_xml_preparedocument @handel output, @x
SELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime',2)
WITH(RecurrenceType varchar(50)) 
/**************结果*****************
EveryMonday
EveryThursday
EverySunday
*/

sample2:

DECLARE @xDailyConfig XML
set @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:00
2000-01-01T13:00:00
2000-01-01T19:00:00
*/

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"...