August 7, 2013 at 12:50 pm
All
I have a column that stores data in a varchar column. The data is easily converted to xml but it has a namespace embedded within the data . I used the value method but I keep geting null for the results for the column when I try to extract the account number. I've tried the namespace option as well.
Data Example:
<p322:ViewStatement xmlns:p322="http://proxy.ws.ejb.mhg.com"><p322:XMLRequest><?xml version="1.0" encoding="utf-8" ?><Request><AccountNumber>123456789</AccountNumber><InternetScreenName>mhg.com.Statements</InternetScreenName><StatementDate>05/20/2013</StatementDate></Request></p322:XMLRequest></p322:ViewStatement>
SQL Example
SELECT TOP 1000 [RequestID]
,cast([Request]as xml) as test
,cast( [Request] as xml).value('(Request/@AccountNumber)[1]', 'Varchar(9)') as AccountNumber
FROM [log]
Any Suggestions?
August 7, 2013 at 1:31 pm
Do you care how you extract the account number? If you just need the account number you could simply use a substring.
August 7, 2013 at 1:41 pm
Thanks for the response. Substring is my fallback if I can't figure out an XML solution.
August 7, 2013 at 2:27 pm
Remove the @ from @AccountNumber
August 7, 2013 at 2:49 pm
SELECT TOP 1000 [RequestID]
,cast([Request]as xml) as test
,cast( [Request] as xml).value('(Request/AccountNumber)[1]', 'Varchar(9)') as AccountNumber
FROM [log]
Returns Null for the accountnumber
August 7, 2013 at 2:52 pm
mhg1063 (8/7/2013)
SELECT TOP 1000 [RequestID],cast([Request]as xml) as test
,cast( [Request] as xml).value('(Request/AccountNumber)[1]', 'Varchar(9)') as AccountNumber
FROM [log]
Returns Null for the accountnumber
Can you send me your results for the initial cast? Inserted the data you provided into a temporary table, this is the query I'm running with results,
[Code="SQL"]SELECT cast([test] AS XML) AS test
, cast([test] AS XML).value('(Request/AccountNumber)[1]', 'VarChar(9)') AS AccountNumber
FROM test2[/Code]
<Request><AccountNumber>123456789</AccountNumber><InternetScreenName>mhg.com.Statements</InternetScreenName><StatementDate>05/20/2013</StatementDate></Request>
123456789
Remember that if you have something like <Reports><Test>test data!</Test></Reports> that you would have to declare the value as /Reports/Test rather than /Reports/@Test. However, if you had something like this, <Reports><Test information="Hello"></Test></Reports> you would would have to declare the value as /Reports/Test/@information. Not sure if this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply