How to get these description from XML into the table?

Question

Post reply

How to get these description from XML into the table?

NewDBA201

SSC Enthusiast

Points: 188
More actions
October 25, 2020 at 12:21 am

#3800819
I want all these description in different row. TIA
Example
descriptions
0|Home
1|Office|False
2|Play Ground
3|School|True
4|Movie Hall
DECLARE @XMLData XML ,@Ret INT
SET @XMLData ='<PlaceDescriptions>
<descriptions>0|Home</descriptions>
<descriptions>1|Office|False</descriptions>
<descriptions>2|Play Ground</descriptions>
<descriptions>3|School|True</descriptions>
<descriptions>4|Movie Hall</descriptions>
></PlaceDescriptions>'
declare @docHandle int
EXEC @Ret = sp_xml_preparedocument @docHandle OUTPUT, @XMLData;
SELECT *
FROM OPENXML(@docHandle, '/PlaceDescriptions/descriptions',3)
WITH ( descriptions VARCHAR(100) '../@descriptions');
SELECT
doc.col.value('descriptions[1]', 'nvarchar(10)') descriptions
FROM @XMLData.nodes('/PlaceDescriptions/descriptions') doc(col)
- This topic was modified 3 years, 6 months ago by NewDBA201.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24285 More actions · Answer 1

Firstly, you don't need to use sp_xml_preparedocument and OPENXML.

Secondly, your xml is not properly formed - There is an extra ">" before "</PlaceDescriptions>"

Below is code that will extract the descriptions from the sample. NOTE: I have kept the data size as nvarchar(10), but it will truncate some of the values. You may want to increase it to nvarchar(20). Also, Do you really need nvarchar? The provided sample data contains no unicode.

DECLARE @XMLData xml;

SET @XMLData = '<PlaceDescriptions>
<descriptions>0|Home</descriptions>
<descriptions>1|Office|False</descriptions>
<descriptions>2|Play Ground</descriptions>
<descriptions>3|School|True</descriptions>
<descriptions>4|Movie Hall</descriptions>
</PlaceDescriptions>';

SELECT  descriptions = doc.col.value( '(text())[1]', 'nvarchar(10)' )
FROM    @XMLData.nodes('/PlaceDescriptions/descriptions') AS doc(col);

This reply was modified 3 years, 6 months ago by DesNorton.

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24285 More actions · Answer 2

You can take this further and split the rows into columns like this

DECLARE @XMLData xml;

SET @XMLData = '<PlaceDescriptions>
<descriptions>0|Home</descriptions>
<descriptions>1|Office|False</descriptions>
<descriptions>2|Play Ground</descriptions>
<descriptions>3|School|True</descriptions>
<descriptions>4|Movie Hall</descriptions>
</PlaceDescriptions>';

WITH cteXML AS (
  SELECT  descriptions = doc.col.value( '(text())[1]', 'varchar(20)' )
  FROM    @XMLData.nodes('/PlaceDescriptions/descriptions') AS doc(col)
)
SELECT  cte.descriptions
      , [id]          = MAX(CASE WHEN stm.ItemNumber = 1 THEN stm.Item END)
      , [description] = MAX(CASE WHEN stm.ItemNumber = 2 THEN stm.Item END)
      , [value]       = MAX(CASE WHEN stm.ItemNumber = 3 THEN stm.Item END)
FROM    cteXML AS cte
CROSS APPLY dbo.DelimitedSplit8K_LEAD(cte.descriptions, '|') AS stm
GROUP BY cte.descriptions;

The code for the splitter can be found here

http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/

How to post data/code on a forum to get the best help.

NewDBA201 SSC Enthusiast Points: 188 More actions · Answer 3

NewDBA201

SSC Enthusiast

Points: 188

October 25, 2020 at 5:32 pm

#3801036

Thank you so much.

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 4

DesNorton wrote:

You can take this further and split the rows into columns like this

DECLARE @XMLData xml;

SET @XMLData = '<PlaceDescriptions>
<descriptions>0|Home</descriptions>
<descriptions>1|Office|False</descriptions>
<descriptions>2|Play Ground</descriptions>
<descriptions>3|School|True</descriptions>
<descriptions>4|Movie Hall</descriptions>
</PlaceDescriptions>';

WITH cteXML AS (
  SELECT  descriptions = doc.col.value( '(text())[1]', 'varchar(20)' )
  FROM    @XMLData.nodes('/PlaceDescriptions/descriptions') AS doc(col)
)
SELECT  cte.descriptions
      , [id]          = MAX(CASE WHEN stm.ItemNumber = 1 THEN stm.Item END)
      , [description] = MAX(CASE WHEN stm.ItemNumber = 2 THEN stm.Item END)
      , [value]       = MAX(CASE WHEN stm.ItemNumber = 3 THEN stm.Item END)
FROM    cteXML AS cte
CROSS APPLY dbo.DelimitedSplit8K_LEAD(cte.descriptions, '|') AS stm
GROUP BY cte.descriptions;

The code for the splitter can be found here

http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/

Niiiccceee followup and great assumption as to what the next logical question might be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)