CODEWITHSUNDEEP
sqlxml
jaekie
jaekie

Reputation: 2303

Turning an XML into a select

I'm trying to turn this XML string into a select

I have @Schedule XML = '<days><day enabled="0">0</day><day enabled="1">1</day><day enabled="1">2</day><day enabled="1">3</day><day enabled="1">4</day><day enabled="1">5</day><day enabled="0">6</day></days>'

What I'm trying to see at the end is..

DayNumber          DayEnabled
0                          0
1                          1
2                          1
3                          1
4                          1
5                          1
6                          0

I've tried a few ways, so far nothing is working right.. I am handling this as an XML data type, I'd prefer not to use a function as this will just be in a stored procedure..

Update: Maybe I didn't explain it correctly.. I have a stored procedure, XML is one of the parameters passed to it, I need to send it to a table to be inserted, so I'm trying to do the following..

INSERT INTO tblDays (DayNumber, DayEnabled)
SELECT @XMLParsedOrTempTableWithResults

I just can't figure out how to parsed the parameter

Upvotes: 0

Views: 84

Answers (2)

Fred Sobotka
Fred Sobotka

Reputation: 5332

The XMLTABLE function is how most XML-enabled DBMSes shred an XML document into a relational result set.

This example uses DB2's syntax for XMLTABLE and an input parameter passed into a stored procedure:

INSERT INTO tblDays (DayNumber, DayEnabled)
SELECT X.* FROM 
    XMLTABLE ('$d/days/day' PASSING XMLPARSE( DOCUMENT SPinputParm ) as "d" 
        COLUMNS 
           dayNumber  INTEGER  PATH '.',
           dayEnabled SMALLINT PATH '@enabled'
    ) AS X
;

Upvotes: 0

SP007
SP007

Reputation: 1921

DECLARE @myXML as XML = '<days><day enabled="0">0</day><day enabled="1">1</day><day      enabled="1">2</day><day enabled="1">3</day><day enabled="1">4</day><day enabled="1">5</day><day enabled="0">6</day></days>'

DECLARE @XMLDataTable table
(
DayNumber int
,DayEnabled int
)

INSERT INTO @XMLDataTable
SELECT d.value('text()[1]','int') AS [DayNumber]
,d.value('(@enabled)[1]','int') AS [DayEnabled]
FROM @myXML.nodes('/days/*') ds(d)

SELECT * FROM @XMLDataTable

Refer:

http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

Upvotes: 1

Related Questions