Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

如何寫XML欄位的日期或數字表達式

1 view
Skip to first unread message

YuQ

unread,
Sep 4, 2009, 5:44:41 AM9/4/09
to
Dear Sirs,

�Ҵ���ʹ��XML��ʽ�탦��Data�����ڙz���r�������ڡ����ֻ��ִ���ȗl����Ӧ�����дSQL ���?
Ո���ˡ�

XML��λ�еă��ݣ�
<level type = 'Scene">
<metadata language_code = "zh-tw">
<column name="Team-Name">���ĈD˹</column>
<column name="Keywords">����,�W�ޱ�</column>
<column name="Title"/>
<column name="Win">4</column>
<column name="Last_session_time">Invalid Date</column>
</metadata>
<metadata language_code = "zh-cn">
<column name="Team-Name">����ͼ˹</column>
<column name="Keywords">����,ŷ�ޱ�</column>
<column name="Title">2009��ŷ�ޱ�����Ԥѡ��</column>
<column name="Win">4</column>
<column name="Last_session_time">Thu Sep 3 2009 03:43:17 PM</column>
</metadata>
</level>

Ҫ��ԃ�ă��ݣ�
1. ����r�g��λ����ָ��Ҫ��춻�С�ij�죬��ij��Datetime
2. Ҫ��ԃݔ�A���Σ����ܴ�춣�С춻���
3. Ҫ��������ƣ�����Keywords������Keyword�����а�


Best regards,

YuQ


Bob

unread,
Sep 4, 2009, 10:39:02 AM9/4/09
to
Sorry, I don't understand. What do your expected results look like?

"YuQ" wrote:

> Dear Sirs,
>
> ÎÒ´òËãʹÓÃXML¸ñʽ 탦´æData£¬µ«ÔÚ™zË÷•r•þÓÐÈÕÆÚ¡¢”µ×Ö»ò×Ö´®°üº¬µÈ—l¼þ£¬Ó¦¸ÃÈçºÎдSQL Óï¾ä?
> Õˆ½ÌÁË¡£
>
> XML™ÚλÖеăÈÈÝ£º


> <level type = 'Scene">
> <metadata language_code = "zh-tw">

> <column name="Team-Name">ÓÈÎĈD˹</column>
> <column name="Keywords">×ãÇò,šWÖÞ±­</column>


> <column name="Title"/>
> <column name="Win">4</column>
> <column name="Last_session_time">Invalid Date</column>
> </metadata>
> <metadata language_code = "zh-cn">

> <column name="Team-Name">ÓÈÎÄͼ˹</column>
> <column name="Keywords">×ãÇò,Å·ÖÞ±­</column>
> <column name="Title">2009Èü¼¾Å·ÖÞ±­×ãÇòԤѡÈü</column>


> <column name="Win">4</column>
> <column name="Last_session_time">Thu Sep 3 2009 03:43:17 PM</column>
> </metadata>
> </level>
>

> Òª²éÔƒµÄƒÈÈÝ£º
> 1. ¸ù“þ•rég™Ú룬²¢Ö¸¶¨Òª´ó춻òСì¶Ä³Ì죬»òij‚€Datetime
> 2. Òª²éԃݔÚAˆö´Î£¬¿ÉÄÜ´ó춣¬Ð¡ì¶»òµÈì¶
> 3. Òª¸ù“þÇò¶ÓÃû³Æ£¬»òÕßKeywords¼ìË÷£¬Keyword¿ÉÄÜÓаüº¬
>
>
> Best regards,
>
> YuQ
>
>
>

YuQ

unread,
Sep 6, 2009, 10:22:00 PM9/6/09
to
Dear Sirs~{#,~}

~{1m~}??~{VPSP~}??~{N;JG~}XML~{#,2i~}?~{JGR*~}??~{6`5D~}??~{VP2i~}?~{5=OkR*5D~}??~{!#6xGR~}?~{<~2;JGFUM(5D~}?~{N;5Hl6#,6xJG~}XML~{VP5D~}?~{N;5Hl6!#~}

~{D?G0RQ~}?~{V*5@WV7{4.~}?~{N;5D0|:,:M5Hl65D2i~}?~{7=7(#(HgOB#)#,5+2;V*5@HUFZ;r~}?~{WV~}?~{N;5D~}?~{@m#,<4OB~}?XML~{5D~}<column name="Win">~{;r~}<column name="Last_session_time">?~{N;5D2i~}?

~{WV7{4.~}?~{N;5D~}SQL?~{>d#,~}?~{V8=L~}
select * from media_log
where
keyframe_set.exist('//metadata/column[@name="Keywords"]/text()[contains(.,"~{WcGr~}")]')=1

?~{6`6`V8=L#,8P~}?~{#!~}

YuQ


"Bob" <B...@discussions.microsoft.com> ~{P4HkO{O"PBNE~}:BC301F97-E528-47B8...@microsoft.com...


> Sorry, I don't understand. What do your expected results look like?
>
> "YuQ" wrote:
>
>> Dear Sirs,
>>

>> ~{NR4rKcJ9SC~}XML~{8qJ=~}??~{4f~}Data~{#,5+TZ~}?~{Kw~}??~{SPHUFZ!"~}?~{WV;rWV4.0|:,5H~}?~{<~#,S&8CHg:NP4~}SQL ~{So>d~}?
>> ?~{=LAK!#~}
>>
>> XML?~{N;VP5D~}?~{H]#:~}


>> <level type = 'Scene">
>> <metadata language_code = "zh-tw">

>> <column name="Team-Name">~{SHND~}?~{K9~}</column>
>> <column name="Keywords">~{WcGr~},?~{V^1-~}</column>


>> <column name="Title"/>
>> <column name="Win">4</column>
>> <column name="Last_session_time">Invalid Date</column>
>> </metadata>
>> <metadata language_code = "zh-cn">

>> <column name="Team-Name">~{SHNDM<K9~}</column>
>> <column name="Keywords">~{WcGr~},~{E7V^1-~}</column>
>> <column name="Title">2009~{H|<>E7V^1-WcGrT$Q!H|~}</column>


>> <column name="Win">4</column>
>> <column name="Last_session_time">Thu Sep 3 2009 03:43:17
>> PM</column>
>> </metadata>
>> </level>
>>

>> ~{R*2i~}?~{5D~}?~{H]#:~}
>> 1. ~{8y~}????~{N;#,2"V86(R*4sl6;rP!l6D3Ll#,;rD3~}?Datetime
>> 2. ~{R*2i~}????~{4N#,?ID\4sl6#,P!l6;r5Hl6~}
>> 33. ~{R*8y~}?~{Gr6SC{3F#,;rU_~}Keywords~{<lKw#,~}Keyword~{?ID\SP0|:,~}
>>
>>
>> Best regards,
>>
>> YuQ
>>
>>
>>


YuQ

unread,
Sep 6, 2009, 10:47:49 PM9/6/09
to
Dear Sirs，
 
目前只知道查询XML中字符串字段的语句，但常用的日期和数字的查询语句还请教各位，望不吝赐教！
 
一下是查询XML中字符串的SQL语句，可以得到结果，还请教更好的。
select asset_id, keyframe_set.query('//metadata/column')
from media_log
where keyframe_set.exist('//metadata/column[@name="Keywords"]/text()[contains(.,"足球")]')=1
 
 
YuQ
 
 
"YuQ" <y...@jvschina.com> 写入消息新闻:eB1aXRUL...@TK2MSFTNGP02.phx.gbl...
> Dear Sirs,
>
> 我打算使用XML格式來儲存Data，但在檢索時會有日期、數字或字串包含等條件，应该如何写SQL 语句?
> 請教了。
>
> XML欄位中的內容：

> <level type = 'Scene">
>    <metadata language_code = "zh-tw">
>        <column name="Team-Name">尤文圖斯</column>
>        <column name="Keywords">足球,歐洲杯</column>

>        <column name="Title"/>
>        <column name="Win">4</column>
>        <column name="Last_session_time">Invalid Date</column>
>    </metadata>
>    <metadata language_code = "zh-cn">
>        <column name="Team-Name">尤文图斯</column>
>        <column name="Keywords">足球,欧洲杯</column>
>        <column name="Title">2009赛季欧洲杯足球预选赛</column>

>        <column name="Win">4</column>
>        <column name="Last_session_time">Thu Sep 3 2009 03:43:17 PM</column>
>    </metadata>
> </level>
>
> 要查詢的內容：
> 1. 根據時間欄位，并指定要大於或小於某天，或某個Datetime
> 2. 要查詢輸贏場次，可能大於，小於或等於
> 3. 要根據球队名称，或者Keywords检索，Keyword可能有包含
>
>
> Best regards,
>
> YuQ
>
>

YuQ

unread,
Sep 9, 2009, 5:24:55 AM9/9/09
to
Dear Sirs��

- �����е�XMLֻ���ڶ��¼��һ�м�¼�µ�XML�ֶ��µ�����
- Ҫͨ��XML�е�Tag��Keyword��Value��ѯ�õ���¼
- Ŀǰ��֪��͵��ڡ��ַ���SQL���

��


select asset_id, keyframe_set.query('//metadata/column')
from media_log
where

keyframe_set.exist('//metadata/column[@name="Keywords"]/text()[contains(.,"�yԇ")]')=1

����
select asset_id,
keyframe_set.query('//metadata/column[@name="Description"]')
from media_log
where keyframe_set.exist('//metadata/column[@name="Description"][. = "���o������"]')=1

Ŀǰ�����������⣺
- ��Ҫ�˽������Where���������ֺ����ڣ������Ǵ����ڡ���ʱ�������ʱ�䣩������


�dz����


YuQ


"YuQ" <y...@jvschina.com> д����Ϣ����:eB1aXRUL...@TK2MSFTNGP02.phx.gbl...

0 new messages