attributes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

attributes

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-27 : 08:51:15

The xml that I am opening to read has the following structure.
I place the xml into a temp table and then try to pull out the data from it.
i.e.
IMAGE, NAME, NUMBRDATA and the ID and NUMBEr of each DOC element

I am not sure how to read the attributes. See my sql below please.

<DATA
IMAGE="some image"
NAME="some name"
NUMBRDATA="some number"
I have a text here
<DOC
ID="some id"
NUMBER="some other number">
</DOC>
<DOC
ID="another id"
NUMBER="something number">
</DOC>
<DOC
ID="some id2"
NUMBER="some number 2">
</DOC>
...
...
...
</DATA>

This is what I have so far:

SELECT
t.c.value('(DOC/NUMBER)[1]', 'VARCHAR(50)') as Data
FROM
#tblDocumentXMLImport AS s
CROSS APPLY s.Data.nodes('/DATA') AS t(c)

This just returns:
"I have a text here"
but I would like to get the attribute values of the elements as indicated above.

Any thoughts please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:00:12

something like


SELECT t.u.value('../IMAGE[1]','varbinary(max)') AS IMAGE,
t.u.value('../NAME[1]','varchar(100)') AS NAME,
t.u.value('../NUMBRDATA[1]','int') AS NUMBRDATA,
t.u.value('./ID[1]','int') AS ID
t.u.value('./ID[1]','int') AS ID,
t.u.value('./NUMBER[1]','int') AS NUMBER
FROM #tblDocumentXMLImport tb
CROSS APPLY youxmlcol.nodes('/DATA/DOC')t(u)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-27 : 09:16:00

quote:

Originally posted by visakh16

something like


SELECT t.u.value('../IMAGE[1]','varbinary(max)') AS IMAGE,
t.u.value('../NAME[1]','varchar(100)') AS NAME,
t.u.value('../NUMBRDATA[1]','int') AS NUMBRDATA,
t.u.value('./ID[1]','int') AS ID
t.u.value('./ID[1]','int') AS ID,
t.u.value('./NUMBER[1]','int') AS NUMBER
FROM #tblDocumentXMLImport tb
CROSS APPLY youxmlcol.nodes('/DATA/DOC')t(u)

Hi,
The returned columns are all NULL
There is indeed data in the xml file.
This should return several records.
Any thoughts please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:27:20

ah...my bad...didnt notice they are attributes

SELECT t.u.value('../@IMAGE[1]','varbinary(max)') AS IMAGE,
t.u.value('../@NAME[1]','varchar(100)') AS NAME,
t.u.value('../@NUMBRDATA[1]','int') AS NUMBRDATA,
t.u.value('./@ID[1]','int') AS ID,
t.u.value('./@NUMBER[1]','int') AS NUMBER
FROM #tblDocumentXMLImport tb
CROSS APPLY youxmlcol.nodes('/DATA/DOC')t(u)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-27 : 09:39:36

Hi,
This does not seem to be returning the correct data.
With slight change to your sql, I can get the DATA attributes in one sql. In another sql, I can get the DOC attributes but only for the first DOC.
How do I get the other DOC values?

I have changed the int type to varchar(50) as I am returning text.
Thanks
The result should be:

DATAIMAGE DATANAME DATANUMBERDATA DOCID DOCNUMBER
some image some name some number some id some other number
some image some name some number anotehr id something number
some image some name some number some id2 some number 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:48:20

try this too


SELECT t.u.value('..[1]/@IMAGE','varbinary(max)') AS IMAGE,
t.u.value('..[1]/@NAME','varchar(100)') AS NAME,
t.u.value('..[1]/@NUMBRDATA','int') AS NUMBRDATA,
t.u.value('.[1]/@ID','int') AS ID,
t.u.value('.[1]/@NUMBER','int') AS NUMBER
FROM #tblDocumentXMLImport tb
CROSS APPLY youxmlcol.nodes('/DATA/DOC')t(u)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-27 : 09:54:00

Hi,
The previous sql works better.
The only issue is:
I have to have one query to get the values for the DATA
Then another sql to get the values for the first DOC
NOT sure how to get the vlaues for the remaining DOC elelemnts.
May be I have to use for loop here inside the xml. I think this is the only way?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 11:55:07

another try


SELECT t.u.value('.[1]/@IMAGE','varbinary(max)') AS IMAGE,
t.u.value('.[1]/@NAME','varchar(100)') AS NAME,
t.u.value('.[1]/@NUMBRDATA','int') AS NUMBRDATA,
v.w.value('.[1]/@ID','int') AS ID,
v.w.value('.[1]/@NUMBER','int') AS NUMBER
FROM #tblDocumentXMLImport tb
CROSS APPLY youxmlcol.nodes('/DATA')t(u)
CROSS APPLY t.u.nodes('/DOC')v(w)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 12:50:18

[code]DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<DATA
IMAGE="some image"
NAME="some name"
NUMBRDATA="some number">
<DOC
ID="some id"
NUMBER="some other number">
</DOC>
<DOC
ID="another id"
NUMBER="something number">
</DOC>
<DOC
ID="some id2"
NUMBER="some number 2">
</DOC>
</DATA>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

Select *
FROM OPENXML (@idoc, '/DATA/DOC',3)
WITH (IMAGE varchar(50) '../@IMAGE',
NAME varchar(50) '../@NAME',
NUMBRDATA Varchar(50)'../@NUMBRDATA',
ID varchar(100) '@ID',
NUMBER varchar(100)'@NUMBER')

EXEC sp_xml_removedocument @idoc[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 13:06:24

--With above Visakh's Crossapply.

DECLARE	@data XML

SET	@data ='
<DATA
IMAGE="some image"
NAME="some name"
NUMBRDATA="some number">
<DOC
ID="some id"
NUMBER="some other number">
</DOC>
<DOC
ID="another id"
NUMBER="something number">
</DOC>
<DOC
ID="some id2"
NUMBER="some number 2">
</DOC>
</DATA>'

SELECT distinct t.u.value('..[1]/@IMAGE','Varchar(100)') AS IMAGE,
t.u.value('..[1]/@NAME','varchar(100)') AS NAME,
t.u.value('..[1]/@NUMBRDATA','varchar(100)') AS NUMBRDATA,
t.u.value('.[1]/@ID','varchar(100)') AS ID,
t.u.value('.[1]/@NUMBER','varchar(100)') AS NUMBER
FROM @data.nodes('/DATA/DOC')as r(c)
CROSS APPLY c.nodes('/DATA/DOC')t(u)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-28 : 02:30:35

quote:

Originally posted by sodeep

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<DATA
IMAGE="some image"
NAME="some name"
NUMBRDATA="some number">
<DOC
ID="some id"
NUMBER="some other number">
</DOC>
<DOC
ID="another id"
NUMBER="something number">
</DOC>
<DOC
ID="some id2"
NUMBER="some number 2">
</DOC>
</DATA>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

Select * 
FROM OPENXML (@idoc, '/DATA/DOC',3)
      WITH (IMAGE  varchar(50) '../@IMAGE',
            NAME	varchar(50) '../@NAME',
            NUMBRDATA Varchar(50)'../@NUMBRDATA',
            ID    varchar(100) '@ID', 
            NUMBER  varchar(100)'@NUMBER')            

EXEC sp_xml_removedocument @idoc

Hi,
What is wrong with this query please because the error is:
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'An invalid character was found in text content.'.
Msg 8179, Level 16, State 5, Line 21
Could not find prepared statement with handle 0.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

This is what I am using:
declare @FullPath varchar(500)
set @FullPath = 'C:\filename.XML'

create table tblImportXML(Data xml)
EXEC('insert into tblImportXML select Data
from
(
SELECT * FROM OPENROWSET(BULK ''' + @FullPath + ''', SINGLE_BLOB) as Disc
) as ImportXML (Data)')

--select * from tblImportXML
declare @idoc int
declare @doc varchar(max)
select @doc = convert(varchar(max), Data) from tblImportXML

drop table tblImportXML

exec sp_xml_preparedocument @idoc output, @doc

Select *
FROM OPENXML (@idoc, '/DATA/DOC',3)
WITH (CLIENT_ID varchar(50) '../@CLIENT_ID',
MATTER_ID varchar(50) '../@MATTER_ID',
DOC_NUMBER Varchar(50)'../@DOC_NUMBER',
CLIENT_ID varchar(100) '@CLIENT_ID',
MATTER_ID varchar(100)'@MATTER_ID',
DOC_NUMBER varchar(100)'@DOC_NUMBER')

EXEC sp_xml_removedocument @idoc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 03:18:11

quote:

Originally posted by sodeep

--With above Visakh's Crossapply.

DECLARE	@data XML

SET	@data ='
<DATA
IMAGE="some image"
NAME="some name"
NUMBRDATA="some number">
<DOC
ID="some id"
NUMBER="some other number">
</DOC>
<DOC
ID="another id"
NUMBER="something number">
</DOC>
<DOC
ID="some id2"
NUMBER="some number 2">
</DOC>
</DATA>'

SELECT distinct t.u.value('..[1]/@IMAGE','Varchar(100)') AS IMAGE,
t.u.value('..[1]/@NAME','varchar(100)') AS NAME,
t.u.value('..[1]/@NUMBRDATA','varchar(100)') AS NUMBRDATA,
t.u.value('.[1]/@ID','varchar(100)') AS ID,
t.u.value('.[1]/@NUMBER','varchar(100)') AS NUMBER
FROM @data.nodes('/DATA/DOC')as r(c)
CROSS APPLY c.nodes('/DATA/DOC')t(u)

if you're retrieving from xml varaible, you dont require cross apply

just below is enough

SELECT t.u.value('..[1]/@IMAGE','varbinary(max)') AS IMAGE,
t.u.value('..[1]/@NAME','varchar(100)') AS NAME,
t.u.value('..[1]/@NUMBRDATA','int') AS NUMBRDATA,
t.u.value('.[1]/@ID','int') AS ID,
t.u.value('.[1]/@NUMBER','int') AS NUMBER
FROM @test.nodes('/DATA/DOC')t(u)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-28 : 03:21:03

Thank you all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 04:00:40

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources