Second problem reading XML from text field

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 2008 Forums

Transact-SQL (2008)

Second problem reading XML from text field

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-20 : 08:24:57

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"?>



<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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-20 : 10:18:53

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

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-20 : 10:52:22

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-20 : 10:55:54

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.

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-20 : 11:05:57

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 !

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-20 : 12:22:37

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.

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-20 : 12:50:17

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-20 : 14:07:41

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources