Simple OPENXML parsing question

[ratinakage]

Hey guys,

If you could run the following SQL command:

I am trying to extract two Addendum elements from the xml string.

DECLARE @string VARCHAR (50)

SET @string = '<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>'

DECLARE @xml_xml_ptr INT

EXEC sp_xml_preparedocument @xml_xml_ptr OUTPUT, @string

SELECT
Addendum
FROM
OPENXML (@xml_xml_ptr, '/Request')
WITH
(
Addendum VARCHAR(79) 'Addendum'
)

EXEC sp_xml_removedocument @xml_xml_ptr

The first error I get is: XML parsing error: The following tags were not closed: Request, Addendum. This seems like valid xml to me.

Any help would be much appreciated.

Thanks!
G

 

[SQLDenis]

that is because your @string VARCHAR (50) is not long enough to store the whole XML which is 79 bytes long

select len('<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>') will give you 79

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ratinakage]

oops.

OK, here's the example:

The problem is, it only returns Addendum1 and not Addendum2...

Let me know if you can see a solution.

---------------------------

DECLARE @string VARCHAR (200)

SET @string = '<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>'

DECLARE @xml_xml_ptr INT

EXEC sp_xml_preparedocument @xml_xml_ptr OUTPUT, @string

SELECT
Addendum
FROM
OPENXML (@xml_xml_ptr, '/Request')
WITH
(
Addendum VARCHAR(79) 'Addendum'
)

EXEC sp_xml_removedocument @xml_xml_ptr

 

[SQLDenis]

give me about two hours I have code at work that I used to do stuff like this (forgot if you need APPLY or not in this case)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ratinakage]

Thanks! Looking foreward to hearing from you...

Let me add the following: If I change my SQL query to the one below, I get a result set of two rows of NULL. In this case, at least it returns two values which I imagine correspond to the two Addendums. Now if I could only get the data inside...

DECLARE @string VARCHAR (150)

SET @string = '<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>'

DECLARE @xml_xml_ptr INT

EXEC sp_xml_preparedocument @xml_xml_ptr OUTPUT, @string

SELECT
Addendum
FROM
OPENXML (@xml_xml_ptr, '/Request/Addendum') -- Added Addendum here...
WITH
(
Addendum VARCHAR(79) -- 'Addendum' -- removed this...
)

EXEC sp_xml_removedocument @xml_xml_ptr

 

[ratinakage]

Found the answer!! - Woo hoo!! No one told me about the '.' - seems like that's what you need. See the corrected SQL below:


DECLARE @string VARCHAR (150)

SET @string = '<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>'

DECLARE @xml_xml_ptr INT

EXEC sp_xml_preparedocument @xml_xml_ptr OUTPUT, @string

SELECT
Addendum
FROM
OPENXML (@xml_xml_ptr, '/Request/Addendum')
WITH
(
Addendum VARCHAR(79) '.'
)

EXEC sp_xml_removedocument @xml_xml_ptr

 

[SQLDenis]

Sorry was in a meeting, this is what I was talking about more or less

DECLARE @string VARCHAR (150)

SET @string = '<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>'

DECLARE @xml_xml_ptr INT         
       
EXEC sp_xml_preparedocument @xml_xml_ptr OUTPUT, @string

SELECT 
   Addendum
FROM
   OPENXML (@xml_xml_ptr, '/Request/Addendum')  -- Added Addendum here...
WITH 
   (
      Addendum VARCHAR(79)  '../Addendum'     )

EXEC sp_xml_removedocument @xml_xml_ptr

output

Addendum1
Addendum1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions