Re: SQL 2005 OPENXML doesnt understand xsi:nil

xsi:nil is an attribute and there are some edge cases, here is a fixed version.


DROP TABLE #Organization

DECLARE @doc VARCHAR(4000)
SET @doc = '<Organization xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<OrganizationID>1</OrganizationID>
<Name>My Travel</Name>
<OrganizationTypeID>1</OrganizationTypeID>
<OrganizationCode xsi:nil="true"></OrganizationCode>
</Organization>'


DECLARE @iprofile INT
exec sp_xml_preparedocument @iprofile output, @doc, '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

SELECT * INTO #Organization
FROM OPENXML (@iProfile, '/Organization',2)
WITH (
[OrganizationID] [bigint] './OrganizationID',
[Name] [nvarchar](100) './Name',
[OrganizationCode] [varchar](30) './OrganizationCode[@xsi:nil!="true" or not(@xsi:nil)]',
[OrganizationTypeID] [smallint] './OrganizationTypeID'
)


SELECT * FROM #Organization

Dan


I don't think OPENXML has a builtin understanding of xsi:nil like
datagrams do. But you can get it to produce the results you want with
an appropriate predicate on the OrganizationCode pattern. You have to
add the xsi namespace to the sp_xml_preparedocument for this to work
Here is an example that reads the OrganizationCode as NULL.

If you are going to be running on SQL Server 2005 though, you might
find it easier to shred using the nodes() function.

--DROP TABLE #Organization

DECLARE @doc VARCHAR(4000)
SET @doc = '<Organization
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<OrganizationID>1</OrganizationID>
<Name>My Travel</Name>
<OrganizationTypeID>1</OrganizationTypeID>
<OrganizationCode xsi:nil="true" />
</Organization>'
DECLARE @iprofile INT
exec sp_xml_preparedocument @iprofile output, @doc, '<root
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'
SELECT * INTO #Organization
FROM OPENXML (@iProfile, '/Organization',2)
WITH (
[OrganizationID] [bigint] './OrganizationID',
[Name] [nvarchar](100) './Name',
[OrganizationCode] [varchar](30)
'./OrganizationCode[xsi:nil!="true"]',
[OrganizationTypeID] [smallint] './OrganizationTypeID'
)
SELECT * FROM #Organization

Dan

I have an xml document that has elements that are nil
(xsi:nil="true") but OPENXML created a column that is empty "", not
NULL. Any ideas why?

<Organization>
<OrganizationID>1</OrganizationID>
<Name>My Travel</Name>
<OrganizationCode xsi:nil="true" />
<OrganizationTypeID>1</OrganizationTypeID>
</Organization>
SELECT * INTO #Organization
FROM OPENXML (@iProfile, '/Organization',2)
WITH (
[OrganizationID] [bigint] './OrganizationID',
[Name] [nvarchar](100) './Name',
[OrganizationCode] [varchar](30) './OrganizationCode',
[OrganizationTypeID] [smallint] './OrganizationTypeID'
)
OrganizationID Name
OrganizationCode
OrganizationTypeID
--------------------
---------------------------------------------------------------------
-
------------------------------
------------------------------ ------------------
1 My Travel
1
OrganizationCode should be NULL



.

Relevant Pages
Loading