問題描述
我在 sql server 中創建了一個示例查詢來解析來自 xml 的數據并立即顯示它.雖然我會在我的表中插入這些數據，但在此之前我面臨一個簡單的問題.
我想在日期時間字段 ADDED_DATE="NULL" 中插入 NULL，如下面給出的 xml 所示.但是當我執行這個查詢時.它給了我錯誤
從字符串轉換日期時間時轉換失敗.
我做錯了什么.請強調我的錯誤.
聲明@xml varchar(1000)設置@xml='<根目錄><TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP><TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP>'聲明@handle intexec sp_xml_preparedocument @handle 輸出，@xmlselect * from OPENXML(@handle,'/ROOT/TX_MAP',1)和(FK_GUEST_ID INT,FK_CATEGORY_ID VARCHAR(10),屬性 VARCHAR(100),[描述] VARCHAR(100),IS_ACTIVE VARCHAR(10),ADDED_BY VARCHAR(100),ADDED_DATE 日期時間 NULL,MODIFIED_BY VARCHAR(100),MODIFIED_DATE 日期時間 NULL)我使用的是 Sql Server 2005.
在谷歌搜索了一個小時后，我得到了我的問題的答案，并想與大家分享，以便對未來的用戶來說變得容易.
聲明@xml varchar(1000)設置@xml='<根目錄><TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="12/3/2010" MODIFIED_BY="NULL" MODIFIED_DATE="12/3/2010"></TX_MAP><TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ></TX_MAP>'聲明@handle intexec sp_xml_preparedocument @handle 輸出，@xmlselect * from OPENXML(@handle,'/ROOT/TX_MAP',1)和(FK_GUEST_ID INT,FK_CATEGORY_ID VARCHAR(10),屬性 VARCHAR(100),[描述] VARCHAR(100),IS_ACTIVE VARCHAR(10),ADDED_BY VARCHAR(100),ADDED_DATE 日期時間,MODIFIED_BY VARCHAR(100),MODIFIED_DATE 日期時間)<塊引用>
你需要做的只是省略將導致的那些屬性NULL 值.
I have created a sample query in sql server to parse data from xml and to display it right now. Although I will be inserting this data in my table but before that I am facing a simple problem.
I want to insert NULL in datetime field ADDED_DATE="NULL" as shown in xml given below. But when I executes this query. It gives me error
Conversion failed when converting datetime from character string.
What mistake am i doing. Please highlight my mistake.
declare @xml varchar(1000)
set @xml= '
<ROOT>
<TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP>
<TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP>
</ROOT> '
declare @handle int
exec sp_xml_preparedocument @handle output, @xml
select * from OPENXML(@handle,'/ROOT/TX_MAP',1)
with
(
FK_GUEST_ID INT
,FK_CATEGORY_ID VARCHAR(10)
,ATTRIBUTE VARCHAR(100)
,[DESCRIPTION] VARCHAR(100)
,IS_ACTIVE VARCHAR(10)
,ADDED_BY VARCHAR(100)
,ADDED_DATE DATETIME NULL
,MODIFIED_BY VARCHAR(100)
,MODIFIED_DATE DATETIME NULL
)
I am using Sql Server 2005.
After googling an hour, I got answer to my question and would like to share with you all so that for future users it become easy.
declare @xml varchar(1000)
set @xml= '
<ROOT>
<TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="12/3/2010" MODIFIED_BY="NULL" MODIFIED_DATE="12/3/2010"></TX_MAP>
<TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ></TX_MAP>
</ROOT> '
declare @handle int
exec sp_xml_preparedocument @handle output, @xml
select * from OPENXML(@handle,'/ROOT/TX_MAP',1)
with
(
FK_GUEST_ID INT
,FK_CATEGORY_ID VARCHAR(10)
,ATTRIBUTE VARCHAR(100)
,[DESCRIPTION] VARCHAR(100)
,IS_ACTIVE VARCHAR(10)
,ADDED_BY VARCHAR(100)
,ADDED_DATE DATETIME
,MODIFIED_BY VARCHAR(100)
,MODIFIED_DATE DATETIME
)
What you need to do is just to omit those attributes that will result into NULL value.
這篇關于如何在sql server中解析xml以處理DateTime DataType中的NULL值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！