Query XML string to extract desired tags - help!

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

General SQL Server Forums

New to SQL Server Programming

Query XML string to extract desired tags - help!

Author

Topic

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 08:41:32

Hi experts, I have the following xml string and I have to select only the QuoteNumber and the UserID from this string: in this case "Q3601-650098" and "woodss". the lenght of the other fields is not fixed. How shall I write my select statement?

Can you help me? Thanks!

<SALES_ORDER_RECORD_IMPORT_CORP><I_HEADER><VKORG>0360</VKORG><VTWEG>01</VTWEG><SPART>60</SPART><BSTNK>P/1930469/0134/Q01Q</BSTNK><REQ_DATE>20100316</REQ_DATE><KUNAG>0310012028</KUNAG><KUNWE>0320001970</KUNWE><KUNNR_ZV>0370001744</KUNNR_ZV>

<QUOTENUMBER>Q3601-650098</QUOTENUMBER>

<PROJECTNUMBER></PROJECTNUMBER><PRICENUMBER></PRICENUMBER><NAME>LORNE STEWART PLC (SVS DIVISIO</NAME><NAME2></NAME2><NAME3></NAME3><NAME4></NAME4><STREET>STEWART HOUSE</STREET><CITY>ORFORD PARK, GREENFOLD WA</CITY><COUNTRY>GB</COUNTRY><POSTL_CODE>WN7 3XJ</POSTL_CODE><TRANSP_ZONE>GBDSND</TRANSP_ZONE>

<USERID>woodss</USERID>

<KUNNR_ZC></KUNNR_ZC><KUNNR_ZN></KUNNR_ZN><AUGRU></AUGRU> etc etc

bhaskarvarada
Starting Member

8 Posts

Posted - 2010-03-25 : 08:57:16

Hi Welsh19,

u can select using the below Query

Declare @xml Xml
Set @xml='<SALES_ORDER_RECORD_IMPORT_CORP>
<I_HEADER>
<VKORG>0360</VKORG>
<VTWEG>01</VTWEG>
<SPART>60</SPART>
<BSTNK>P/1930469/0134/Q01Q</BSTNK>
<REQ_DATE>20100316</REQ_DATE>
<KUNAG>0310012028</KUNAG>
<KUNWE>0320001970</KUNWE>
<KUNNR_ZV>0370001744</KUNNR_ZV>
<QUOTENUMBER>Q3601-650098</QUOTENUMBER>
<PROJECTNUMBER></PROJECTNUMBER>
<PRICENUMBER></PRICENUMBER>
<NAME>LORNE STEWART PLC (SVS DIVISIO</NAME>
<NAME2></NAME2>
<NAME3></NAME3>
<NAME4></NAME4>
<STREET>STEWART HOUSE</STREET>
<CITY>ORFORD PARK, GREENFOLD WA</CITY>
<COUNTRY>GB</COUNTRY>
<POSTL_CODE>WN7 3XJ</POSTL_CODE>
<TRANSP_ZONE>GBDSND</TRANSP_ZONE>
<USERID>woodss</USERID>
<KUNNR_ZC></KUNNR_ZC>
<KUNNR_ZN></KUNNR_ZN>
<AUGRU></AUGRU>
</I_HEADER>
</SALES_ORDER_RECORD_IMPORT_CORP>'
select @xml

select
nref.value('QUOTENUMBER[1]','varchar(200)') QUOTENUMBER,
nref.value('USERID[1]','varchar(200)') USERID
FROM @xml.nodes('//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER') as P(nref)

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 09:14:38

I have to load these fields into an OLE database using a SQL ODBC connection. So my issue is that on my tool I can write a SQL statement but I can't "declare a function". Any advice?

Thanks!

bhaskarvarada
Starting Member

8 Posts

Posted - 2010-03-25 : 09:37:51

HI Welsh19,

with ref to my above reply

i have inserted the XML into a table as below

Create table #t(Xmlvalue XML)
insert into #t(Xmlvalue)
Select @xml

From the below Query u can fetch the values of an XML field

SELECT Xmlvalue.value('(//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER/QUOTENUMBER)[1]', 'varchar(300)') QUOTENUMBER
,Xmlvalue.value('(//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER/UserID)[1]', 'nvarchar(300)') USERID
FROM #t

Rgds,
Bhaskar

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 10:07:34

I'm trying with :

Declare @xml Xml
Set @xml= (SELECT Request FROM SAPLog)
select @xml

select
nref.value('QUOTENUMBER[1]','varchar(200)') QUOTENUMBER,
nref.value('USERID[1]','varchar(200)') USERID
FROM @xml.nodes('//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER') as P(nref)

but it returns :

Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
--------------------
NULL

(1 row(s) affected)

QUOTENUMBER USERID ---------------------------------------------------------

(0 row(s) affected)

What are the mistakes?

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 10:19:46

I need to read 2 fields from an XML string

To summarize:

I have a SQL table called SAPLog that has a field called "Request": this field is an XML string that contains multiple info like the <QUOTENUMBER>Q3601-650098</QUOTENUMBER> and <USERID>woodss</USERID>.

I need to run a query on this table/field to read in my OLE database only the QuoteNumber and the UserID.

Questions:

1) How do I declare as xml the field "Request" from the table "SAPLog"?

2) What should the select statement look like? If I run your query directly in SQL it returns the QuoteNumber only but not the UserID.

Any help?

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 11:07:11

I think I can't use the function SUBSTRING because the lenght of the xml string is variable.

Let's say I have a txt string on each record:

rec1 <Name>welsh19</Name><Surname>Unknown</Surname>
rec2 <Name>sqlexpert1</Name><Surname>Hidden</Surname>

Is there a way to retrieve from each record the text contained between <Surname> and </Surname>, assuming that the lenght of the name varies from record to record???

In the case above the result should be:

Unknown
Hidden

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:18:10

so are you trying to return these values for each record in a table with xml column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:23:06

some thing like


SELECT nref.value('QUOTENUMBER[1]','varchar(200)') QUOTENUMBER,
nref.value('USERID[1]','varchar(200)') USERID
FROM YourTable t
CROSS APPLY xmlfield.nodes('//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER') as P(nref)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 11:31:07

Tried with:

SELECT nref.value('QUOTENUMBER[1]','varchar(200)') QUOTENUMBER,
nref.value('USERID[1]','varchar(200)') USERID
FROM SAPLog t
CROSS APPLY xmlfield.nodes('//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER') as P(nref)

but it returns with the following error message:

Server: Msg 207, Level 16, State 1, Line 4
Invalid column name 'xmlfield'.
Server: Msg 9506, Level 16, State 1, Line 4
The XMLDT method 'nodes' can only be invoked on columns of type xml.

Am I missing something?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:33:11

quote:
Originally posted by welsh19

Tried with:

SELECT nref.value('QUOTENUMBER[1]','varchar(200)') QUOTENUMBER,
nref.value('USERID[1]','varchar(200)') USERID
FROM SAPLog t
CROSS APPLY xmlfield.nodes('//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER') as P(nref)

but it returns with the following error message:

Server: Msg 207, Level 16, State 1, Line 4
Invalid column name 'xmlfield'.
Server: Msg 9506, Level 16, State 1, Line 4
The XMLDT method 'nodes' can only be invoked on columns of type xml.

Am I missing something?

you need to replace xmlfield with name of your actual xml field in table (i dont know it thats why i put a generic name)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 11:43:35

Ok replaced with the name of the field ("Request"):

SELECT nref.value('QUOTENUMBER[1]','varchar(200)') QUOTENUMBER,
nref.value('USERID[1]','varchar(200)') USERID
FROM SAPLog t
CROSS APPLY Request.nodes('//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER') as P(nref)

but it returns with the following:

Server: Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.

What's the trick now?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:45:52

isnt Request a xml type column ( I thought thats what you told in earlier post)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 11:50:26

It's probably just a text field with tags. I though it was an xml.

Let's make it simple with a short example:

I have a txt string on each record:

rec1 <Name>welsh19</Name><Surname>Unknown</Surname>
rec2 <Name>sqlexpert1</Name><Surname>Hidden</Surname>

Is there a way to retrieve from each record the text contained between and , assuming that the lenght of the name varies from record to record???

In the case above the result should be:

rec1 Unknown
rec2 Hidden

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:52:21

what about this?


SELECT nref.value('QUOTENUMBER[1]','varchar(200)') QUOTENUMBER,
nref.value('USERID[1]','varchar(200)') USERID
FROM (SELECT CAST(Request as xml) AS Request FROM SAPLog) t
CROSS APPLY Request.nodes('//SALES_ORDER_RECORD_IMPORT_CORP/I_HEADER') as P(nref)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 11:54:52

Again error message:

Server: Msg 9412, Level 16, State 1, Line 1
XML parsing: line 1, character 199, '>' expected

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:05:29

quote:
Originally posted by welsh19

Again error message:

Server: Msg 9412, Level 16, State 1, Line 1
XML parsing: line 1, character 199, '>' expected

ok that means your xml values contained in column is not well formed. so you've no other way other than


SELECT SUBSTRING(Request,PATINDEX('%<QUOTENUMBER>%',Request)+13, PATINDEX('%<\QUOTENUMBER>%',Request)-PATINDEX('%<QUOTENUMBER>%',Request)-13) AS QUOTENUMBER,
SUBSTRING(Request,PATINDEX('%<USERID>%',Request)+8, PATINDEX('%<\USERID>%',Request)-PATINDEX('%<USERID>%',Request)-8) AS USERID
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 12:47:13

Thanks for your help. Unfortunately I still get an error regarding the lenght:

SELECT SUBSTRING(Request,PATINDEX('%<QUOTENUMBER>%',Request)+13, PATINDEX('%<\QUOTENUMBER>%',Request)-PATINDEX('%<QUOTENUMBER>%',Request)-13) AS QUOTENUMBER,
SUBSTRING(Request,PATINDEX('%<USERID>%',Request)+8, PATINDEX('%<\USERID>%',Request)-PATINDEX('%<USERID>%',Request)-8) AS USERID
FROM SAPLog

Result:

Server: Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:49:45

[code]SELECT
CASE WHEN PATINDEX('%<QUOTENUMBER>%',Request) > 0 THEN
SUBSTRING(Request,PATINDEX('%<QUOTENUMBER>%',Request)+13, PATINDEX('%<\QUOTENUMBER>%',Request)-PATINDEX('%<QUOTENUMBER>%',Request)-13)
ELSE NULL END AS QUOTENUMBER,
CASE WHEN PATINDEX('%<USERID>%',Request) > 0 THEN
SUBSTRING(Request,PATINDEX('%<USERID>%',Request)+8, PATINDEX('%<\USERID>%',Request)-PATINDEX('%<USERID>%',Request)-8)
ELSE NULL END AS USERID
FROM SAPLog
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

welsh19
Starting Member

14 Posts

Posted - 2010-03-25 : 12:54:23

Result:

QUOTENUMBER --------------------
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

Server: Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:56:27

i certainly feel your xml data is not correct, its missing some closing tags it seems which is breaking code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources