New answers tagged xml
8
When working with XML in SQL Server you use the xml Data Type Methods and when shredding XML documents you typicly use the nodes() and value() methods. The XML you have here also include a number of namespaces so you have to specify the ones you need using WITH XMLNAMESPACES (Transact-SQL).
The XML is quite complex so without knowing how you want the data ...
1
@elimerv,
Are you sure you are not having something else in place of "single-quote" that is expected there? Also, you don't tell what kind of data does the "pm" table has in the pm_id column (BTW, your latest SQL doesn't appear to be correct as your sample_data subquery has an "id" column whereas your main query refers to "pm_id" column).
This is what I get ...
0
SQL> with sample_data as (
2 select id
3 from pm
4 where p_id=30369
5 )
6 select id, full_name from users where id in(
7 select cast(t.column_value.extract('//text()') as varchar2(400)) val
8 from sample_data,
9 ...
0
I think Phil is on the right track. You need to move the CTE ("WITH clause") out of the sub-select
Try this one:
with sample_data as (
select pm_id
from pm
where p_id=30369
)
select id,
full_name
from users
where id in(
select cast(t.column_value.extract('//text()') as varchar2(40)) val
from sample_data,
...
0
I think the way you'll have to do it is a Jokke suggest: instead of defining the DT datatype as TIMESTAMP WITH TIME ZONE, rather define it as VARCHAR2(29) where I think that 29 characters should be enough but you could define it wider.
Then when you extract data from the XML you'll have to convert it to TIMESTAMP WITH TIME ZONE.
However, you could also ...
4
Without an ORDER BY clause, the order the values are returned in will be unreliable, as they can be influenced by indexes and the data pages the individual rows are stored on.
If you require the data to be in a particular order, use the ORDER BY clause.
0
If you don't find out how to manage it with SQL Loader you could add a varchar column to table where you are importing and add an insert tigger which then converts that varchar column to some date column in the table.
Top 50 recent answers are included
