SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Second problem reading XML from text field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

173 Posts

Posted - 08/20/2013 :  08:24:57  Show Profile  Reply with Quote
Yestedday James K kindly helped out with my query reading XML from a text field when the XML file has a namespace ( below )
WITH XMLNAMESPACES (DEFAULT 'http://bacs.co.uk/submissions')
SELECT
t.u.value('(./@status)[1]','varchar(20)') AS Result,
t.u.value('(./@index)[1]','int') AS FileIndex,
t.u.value('(./@paymentFileIdentifier)[1]','varchar(6)') AS FileSerialNumber,
t.u.value('(./@processingDay)[1]','varchar(10)') AS ProcessingDate ,
t.u.value('(./@creditRecordCount)[1]','int') AS CreditCount,
t.u.value('(./@creditValueTotal)[1]','varchar(11)') AS CreditTotal,
t.u.value('(./@debitRecordCount)[1]','int') AS DebitCount,
t.u.value('(./@debitValueTotal)[1]','varchar(11)') AS DebitTotal,
t.u.value('(./@ddiRecordCount)[1]','int') AS AUDDISCount,
t.u.value('(./OriginatingServiceUser/@userNumber)[1]','varchar(6)') AS SUN,
t.u.value('(./OriginatingServiceUser/@name)[1]','varchar(18)') AS OAccountName,
t.u.value('(../SigningContact/@fullName)[1]','varchar(30)') AS FileSignedBy,
t.u.value('(../SubmittingContact/@fullName)[1]','varchar(30)') AS FileSubmittedBy,
UPPER(t.u.value('(../@submissionType)[1]','varchar(30)')) AS SubmissionType,
t.u.value('(../@submissionSerialNumber)[1]','varchar(30)') AS SubmissionSerialNumber,
t.u.value('(../@submissionIdentifier)[1]','varchar(100)') AS BacsSubmissionIdentifier
FROM
(
SELECT CAST(CAST(SubmissionResults AS NTEXT) AS XML) AS Xmlreport
FROM [TESTDB].[dbo].[TESTTABLE] WHERE SerialNumber = 9
) d
CROSS APPLY Xmlreport.nodes('/SubmissionResults/PaymentFile')t(u)

I have now run across a similar problem and have spend hours trying to resolve it. n this instance i am just trying to recover the time as a varchar although I will need to expand the query. I just need to get a start point ie a working piece of code

SELECT
t.a.value('(@time)[1]', 'varchar(10)') as 'time'

FROM
(SELECT CAST(CAST(XMLText AS NTEXT) AS XML) AS Xmlreport
FROM [TESTDB].[dbo].[TESTTABLE] WHERE [Id] = '56561407') d
CROSS APPLY Xmlreport.nodes('//Data/InputReport/Header/ProducedOn') t(a)


Data from the XMLText field. I have layed it out in here to make it easier to read

<?xml version="1.0" encoding="UTF-8"?>

<!-- Generated by Oracle Reports version 10.1.2.3.0 -->

<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-reports.xsd">

<Data>

<InputReport>

<Header reportNumber="1" reportType="REFT2013">
<ProducedOn date="2012-03-30" time="08:41:08"></ProducedOn>
<ProcessingDate date="2012-03-30"></ProcessingDate>
</Header>

</InputReport>

</Data>
</BACSDocument>

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 08/20/2013 :  10:18:53  Show Profile  Reply with Quote
Here are two or three different ways to get that time information.
DECLARE @x XML = '
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-reports.xsd">
  <Data>
    <InputReport>
      <Header reportNumber="1" reportType="REFT2013">
        <ProducedOn date="2012-03-30" time="08:41:08" />
        <ProcessingDate date="2012-03-30" />
      </Header>
    </InputReport>
  </Data>
</BACSDocument>'

-- 1
SELECT
	a.value('@time','varchar(10)') AS [time]
FROM
	@x.nodes('//ProducedOn') T(a)

-- 2
SELECT
	a.value('(Data/InputReport/Header/ProducedOn)[1]/@time','varchar(10)') AS [time]
FROM
	@x.nodes('/BACSDocument') T(a)
	
-- 3
SELECT
	a.value('(InputReport/Header/ProducedOn)[1]/@time','varchar(10)') AS [time]
FROM
	@x.nodes('/BACSDocument/Data') T(a)	
	

-- 4
SELECT
	a.value('(//ProducedOn)[1]/@time','varchar(10)') AS [time]
FROM
	@x.nodes('/BACSDocument/Data') T(a)


The way I do this is to first format the XML nicely so you can visually see how the XML looks like.

The First method is probably NOT what you want. I wrote that there only to show what it is doing. If you look in the FROM clause of that query, it has a '//ProducedOn'. What that says is, "Go down the tree and find the "ProducedOn" nodes, no matter where in the tree they arey. Then, the select clause says, give me the time attribute of that node.

Note that I don't even have the indexer (as in (@time)[1]) there. That is because a node can have only one attribute with a given name, so there is no need to pick one.

The second method is giving an exact path. It says travel down to BACSDocument (in the FROM clause), and then from there, navigate down to Data/InputReport/Header/ProducedOn. There could be multiple nodes that satisfy this condition, and value function requires one node that it can work with. Hence the (Data/InputReport/Header/ProducedOn)[1] indexer. Then, once you are there, find the time attribute.

The third would produce the same result in this case, but if you had multiple Data nodes, it would produce one row for each Data node. Because in this case, FROM clause takes you down to Data node.

Fourth is another variation. Add couple more Data nodes, Header nodes etc. and you will see how each differs
Go to Top of Page

Pete_N
Posting Yak Master

173 Posts

Posted - 08/20/2013 :  10:52:22  Show Profile  Reply with Quote
Hi James,

Thank you once again for your help. The XML file is pretty ;large with loads of different nodes, but you have given me some great examples to explore and test

thanks one again
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 08/20/2013 :  10:55:54  Show Profile  Reply with Quote
You are very welcome - hope that helps.

I didn't mean to suggest that you should format the complete XML file. Pick a small representative sample and understand that, so you know what the layout is. You can also generate the schema from the XML or even ask the people who produced the XML if they have the schema, so you can understand how the data is structured.
Go to Top of Page

Pete_N
Posting Yak Master

173 Posts

Posted - 08/20/2013 :  11:05:57  Show Profile  Reply with Quote
Hi James,

Im having a senior moment here, the xml file is stored in a text file, how do i load that into the @x

Its been a long day !
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 08/20/2013 :  12:22:37  Show Profile  Reply with Quote
If it is a one-time experiment, you can just copy and paste it. Otherwise, import the data into a table. This page shows code for importing: http://technet.microsoft.com/en-us/library/ms191184.aspx

If the data is in a table, instead of "FROM @x.nodes('/BACSDocument') T(a)", you would use "FROM YourTableName a CROSS APPLY a.XMLColumn.nodes('/BACSDocument') T(a) ". If the column is not XML type, you would need to cast it just like you had in the example you posted above.
Go to Top of Page

Pete_N
Posting Yak Master

173 Posts

Posted - 08/20/2013 :  12:50:17  Show Profile  Reply with Quote
Hi James,

I'm just not getting it today.

The XMLTest files is a text field. This isn't a one off, in fact ther will be over a hundred records added daily, and there is loads of nodes i need to get info from.

It seemed so simple yesterday when there was a name space .. and unfortunately i can not get the xml changed. It is placed in the database by a third party
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 08/20/2013 :  14:07:41  Show Profile  Reply with Quote
I misunderstood. I thought the data was in TEXT FILE rather than a TEXT column in a table. If it is in a table, it is easy - cast the text column as XML and use it. See the example below:

CREATE TABLE #tmp(c1 TEXT)
INSERT INTO #tmp VALUES
('<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-reports.xsd">
  <Data>
    <InputReport>
      <Header reportNumber="1" reportType="REFT2013">
        <ProducedOn date="2012-03-30" time="08:41:08" />
        <ProcessingDate date="2012-03-30" />
      </Header>
    </InputReport>
  </Data>
</BACSDocument>'
 )

SELECT
	a.value('(Data/InputReport/Header/ProducedOn)[1]/@time','varchar(10)') AS [time]
FROM
	(SELECT CAST(c1 AS XML) AS c1XML FROM #tmp) t1
	CROSS APPLY c1XML.nodes('/BACSDocument') T2(a)
DROP TABLE #tmp;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000