XML to SQL Server Insert

Question

I want to insert this XML data into SQL Server 2008

<rewriteMap name="OldStaticUrl">
   <add key="/about/" value="/about" />
   <add key="/2march2012" value="/community/0770/welcome-to-electronic-musicians-march-2012-links-page/147452" />
</rewriteMap>

I am using this SQL query

DECLARE @hdoc INT
DECLARE @doc varchar(1000)

SET @doc = '<rewriteMap name="OldStaticUrl">
               <add key="/about/" value="/about" />
               <add key="/2march2012" value="/community/0770/welcome-to-electronic-musicians-march-2012-links-page/147452" />
            </rewriteMap>'

 EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

 SELECT vanity, originalurl
  FROM Openxml(@hdoc, '/rewriteMap/add',1) 
  WITH (vanity varchar(1000), originalurl varchar(1000))

But I am getting null in the result set, in fact if I use select * from openxml, I get multiple rows, some of which contains proper data.

luviktor · Accepted Answer · 2012-02-06 20:43:03Z

3

Instead of vanity and originalurl literals you should use key and value respectively.
Try this query:

DECLARE @hdoc INT
DECLARE @doc varchar(1000)
SET @doc = '<rewriteMap name="OldStaticUrl">
                <add key="/about/" value="/about" />
                <add key="/2march2012" value="/community/0770/welcome-to-electronic-musicians-march-2012-links-page/147452" />
            </rewriteMap>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT [key], value
FROM Openxml( @hdoc, '/rewriteMap/add', 1) WITH ( [key] varchar(1000), value varchar(1000) )

edited Feb 6, 2012 at 20:43

answered Feb 6, 2012 at 20:36

luviktor

2,2682 gold badges22 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XML to SQL Server Insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related