Re: SQL 2005 OPENXML doesnt understand xsi:nil

Dan is right. OpenXML operates on an untyped XML DOM tree. Thus xsi:nil is
just another attribute...

The nodes() method in SQL Server 2005 will understand xsi:nil if you have
the XML datatype instance schema validated.

Best regards
Michael

"Dan Sullivan" <danATpluralsight.com> wrote in message
news:964a9ae61ff598c8731b3f418748@xxxxxxxxxxxxxxxxxxxxx
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

  • Re: Loading XML to SLQ Server
    ... "In SQL Server 2000 you have two main options - using the OPENXML technology ... BulkLoad runs on the client and requires no T-SQL but you need to generate ...
    (microsoft.public.sqlserver.xml)
  • Re: Have Insert statement, need equivalent Update.
    ... All the relevent openxml is there I just couldn't figure out how to ... INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID, ... FROM tblScan, tblScanAttribute JOIN #temp ON ... SQL Server, ...
    (comp.databases.ms-sqlserver)
  • Re: Have Insert statement, need equivalent Update.
    ... All the relevent openxml is there I just couldn't figure out how to ... INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID, ... FROM tblScan, tblScanAttribute JOIN #temp ON ... SQL Server, ...
    (comp.databases.ms-sqlserver)
  • Re: Recommendations for shredding RSS to SQL Server?
    ... In SQL Server 2000 you have two main options - using the OPENXML technology ... BulkLoad runs on the client and requires no T-SQL but you need to generate ...
    (microsoft.public.sqlserver.xml)
Loading