• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 928
  • Last Modified:

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:

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_24897455.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

0
yetti78
Asked:
yetti78
  • 2
  • 2
1 Solution
 
brejkCommented:
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

0
 
brejkCommented:
More elegant solution:
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 XMLNAMESPACES ('http://www.fooinc.com' AS foo), _incomingData AS (
SELECT
items.item.value('(@foo:lID)[1]', 'int') AS LID,
items.item.value('(title)[1]', 'varchar(255)') AS title,
items.item.value('(foo:statusType/text())[1]','int') AS statusType
FROM @inputInfo.nodes('/item') AS items(item)
)
SELECT * FROM _incomingData;

Open in new window

0
 
yetti78Author Commented:
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!!!

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_25031654.html
0
 
yetti78Author Commented:
Bravo!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now