asked on

Shredding XML into SQL Server With Multiple Namespaces

Okay, have XML that contains both non-namespace and custom namespace elements. Trying to shred it into SQL Server 2008 Enterprise, and I've gone through this:

https://www.experts-exchange.com/questions/24897455/XML-parsing-line-27-character-86-unexpected-end-of-input.html

But I want to be able to work with both namespace and non-namespace elements:

------------------------------
CREATE PROC [dbo].[listings_save]
(
@inputData XML
)
AS

SET NOCOUNT ON

;WITH _incomingData AS
(

SELECT
items.item.value('(@lID)[1]', 'INT') AS LID,
items.item.value('(title)[1]', 'VARCHAR(255)') AS title,
items.item.value('foo:statusType[1]', 'INT') AS statusType
FROM @inputInfo.nodes('declare namespace foo="http://www.fooinc.com"; /item') AS items(item)
)
-------------------------------

How can I get work with all elements equally to get them into the CTE above?

<item foo:lID="0" xmlns:foo="http://www.fooinc.com">
      <title>An Item to Buy</title>
      <foo:statusType>1</foo:statusType>
      <foo:test>icle</foo:test>
      <guid>12345</guid>
    </item>

Open in new window

brejk

This is ugly, but you can repeat the namespace declaration in the value method:

DECLARE @inputInfo xml;
SET @inputInfo = '<item foo:lID="0" xmlns:foo="http://www.fooinc.com"> 
<title>An Item to Buy</title> 
<foo:statusType>1</foo:statusType> 
<foo:test>icle</foo:test> 
<guid>12345</guid> 
</item>';

SET NOCOUNT ON

;WITH _incomingData AS
(
  SELECT
  items.item.value('declare namespace foo="http://www.fooinc.com";(@foo:lID)[1]', 'int') AS LID,
  items.item.value('(title)[1]', 'varchar(255)') AS title,
  items.item.value('declare namespace foo="http://www.fooinc.com";(foo:statusType/text())[1]','int') AS statusType
  FROM @inputInfo.nodes('/item') AS items(item)
) 
SELECT * FROM _incomingData;

Open in new window

ASKER CERTIFIED SOLUTION

brejk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yetti78

ASKER

brejk,

Well done. When you have a second, this is a new issue I'm having trouble with if you'd like to take a stab at. Thanks!!!

https://www.experts-exchange.com/questions/25031654/Performing-Multiple-Merges-in-SQL-Server-2008-Stored-Procedure-With-Shredded-XML.html

yetti78

ASKER

Bravo!