SQL Server XML Shredding Part 4


You may have a problem using OPENXML. You may get NULL returned. You don’t get an error in SSMS, you just get NULL returned instead of your data. Here is a script that shows you the XML data and two different solutions to the problem. At the bottom of the script we show how to insert into a table the data from the XML.

Notice that in our XML we are using both attributes (PersonID=”4″) and elements (LastName and FirstName). Notice that we switched the order in the third one, called “solution” in the comments. The first two use OPENXML and the rest of them do not. It looks like nodes() solved the problem.

SELECT … FROM … nodes()

DECLARE @xml XML;
DECLARE @xmlHandle INT;  -- a pointer to the internal representation
SET @xml = '
<People>
	<Person PersonID="4">
		<LastName>Jackson</LastName>
		<FirstName>John</FirstName>
	</Person>
	<Person PersonID="10">
		<LastName>Johnson</LastName>
		<FirstName>Jack</FirstName>
	</Person>
</People>'
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xml;
-- OPENXML returns nulls -------------------------------------------------------
SELECT * FROM OPENXML(@xmlHandle,'/People/Person')
  WITH (PersonID Varchar(20),
        LastName Varchar(30),
        FirstName Varchar(30));
-- by the way, you do not need to query them all -----------------------------------------
SELECT * FROM OPENXML(@xmlHandle,'/People/Person')
  WITH (PersonID Varchar(20),
        LastName Varchar(30));
-- solution --------------------------------------------------------------------
SELECT
    PersonID = xc.value('@PersonID', 'int'),
    FirstName = xc.value('(FirstName)[1]', 'varchar(50)'),
    LastName = xc.value('(LastName)[1]', 'varchar(50)')
FROM  @xml.nodes('/People/Person') AS XT(XC)
-- alternate solution -----------------------------------------------------------
SELECT PersonID = Container.value('(@PersonID)[1]', 'varchar(50)'),
       LastName = Container.value('(LastName)[1]', 'varchar(50)'),
       FirstName = Container.value('(FirstName)[1]', 'varchar(50)')
FROM   (SELECT @xml Columndata) a
       CROSS APPLY Columndata.nodes('/People/Person') AS T(Container) 
-- we can re-name the output to Col1, Col2 and Col3 or whatever ---------
SELECT
    Col1 = xc.value('@PersonID', 'int'),
    Col2 = xc.value('(FirstName)[1]', 'varchar(50)'),
    Col3 = xc.value('(LastName)[1]', 'varchar(50)')
FROM 
    @xml.nodes('/People/Person') AS XT(XC)
-- insert into a table called Persons ----------------
INSERT INTO Persons (PersonID, FirstName, LastName)
SELECT
    PersonID = xc.value('@PersonID', 'int'),
    FirstName = xc.value('(FirstName)[1]', 'varchar(50)'),
    LastName = xc.value('(LastName)[1]', 'varchar(50)')
FROM  @xml.nodes('/People/Person') AS XT(XC)

Below is what the output in SSMS looks like.

Series Navigation<< SQL Server XML Shredding Part 3SQL Server XML Shredding Part 5 >>

Leave a comment

Your email address will not be published. Required fields are marked *