Joining XQuery Results - 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 2008 Forums

Transact-SQL (2008)

Joining XQuery Results

Author

Topic

cpender
Starting Member

11 Posts

Posted - 2010-07-06 : 08:50:56

Hi below are three XQueries and their assocatied results:

XQuery #1:

SELECT t.a.value('(./@desc)[1]', 'nvarchar(50)')

FROM SampleTable
CROSS APPLY XMLData.nodes('

declare default element namespace "SIMATIC_BATCH_V7_0_1";

//Cr/Modifcltn/Modif

') AS t(a)

WHERE ID=132;

XQuery #1 Results:

Batch created ( ID: 132 / 59 ), Name: WXTQ153 25%
Scaling the control recipe
released
running
completed
Close batch
Batch closed

XQuery #2

SELECT t.a.value('(./@time)[1]', 'datetime')

FROM SB6_221_96_2936103_Archive.dbo.tblBatches_XML
CROSS APPLY XMLData.nodes('

declare default element namespace "SIMATIC_BATCH_V7_0_1";

//Cr/Modifcltn/Modif

') AS t(a)

WHERE ID=132;

XQuery #2 Results:

2010-04-22 09:37:07.000
2010-04-22 09:37:16.000
2010-04-22 09:37:17.000
2010-04-22 09:37:34.000
2010-04-22 14:43:51.000
2010-04-22 16:50:12.000
2010-04-22 16:56:37.000

XQuery #3:

SELECT t.a.value('(./@loginname)[1]', 'nvarchar(50)')

FROM SB6_221_96_2936103_Archive.dbo.tblBatches_XML
CROSS APPLY XMLData.nodes('

declare default element namespace "SIMATIC_BATCH_V7_0_1";

//Cr/Modifcltn/Modif

') AS t(a)

WHERE ID=132;

XQuery #3 Results:

OSS10131\bep
OSS10131\bep
NULL
NULL
NULL
OSS10131\v056
(CDV)

Now, what I would like to do is combine/join these three results into one table. I don't think it is possible to use JOIN, LEFT JOIN etc becaue there is no realtionship between the results.

Does anyone have any idea how to do this? Is it possible to combine all my XQueries into one XQuery?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 09:38:56

Perhaps you should show us the XML content and your expected output?

N 56°04'39.26"
E 12°55'05.63"

cpender
Starting Member

11 Posts

Posted - 2010-07-06 : 10:27:24

Here's the XML I'm Querying:

<Archivebatch xmlns="SIMATIC_BATCH_V7_0_1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" pcellid="0" pcellname="Plant" xmlversion="7.0.1.0" dbident="SB6_221-96-2936103" time="2010-05-20T16:14:42.609Z">
  <Cr id="132" name="WXTQ153 25%" creationdate="2010-04-22T09:36:25.000Z" modifieddate="2010-04-22T16:56:36.000Z" quantity="1" formulacategoryname="Production" formulaname="Production">
    <Modifcltn>
      <Modif id="1" modiftype="12" time="2010-04-22T09:37:07.000Z" desc="Batch created ( ID: 132 / 59 ), Name: WXTQ153 25%" loginname="OSS10131\bep" fullloginname="Bo Petersen" computername="OSS10131" />
      <Modif id="2" modiftype="2" time="2010-04-22T09:37:16.000Z" desc="Scaling the control recipe" loginname="OSS10131\bep" fullloginname="Bo Petersen" computername="OSS10131" />
      <Modif id="3" modiftype="29" time="2010-04-22T09:37:17.000Z" desc="released" computername="OSS10131" />
      <Modif id="4" modiftype="29" time="2010-04-22T09:37:34.000Z" desc="running" computername="OSS10131" />
      <Modif id="5" modiftype="29" time="2010-04-22T14:43:51.000Z" desc="completed" computername="OSS10131" />
      <Modif id="6" modiftype="29" time="2010-04-22T16:50:12.000Z" desc="Close batch" loginname="OSS10131\v056" fullloginname="v056" computername="OSS10131" />
      <Modif id="7" modiftype="29" time="2010-04-22T16:56:37.000Z" desc="Batch closed" loginname="(CDV) " fullloginname="BATCH CDV" computername="OSS10131" />
    </Modifcltn>
  </Cr>
</Archivebatch>

The output I would like it something like (excuse the formatting):

2010-04-22 09:37:07.000 created(ID:132/59),Name:WXTQ153 25% OSS10131\bep
2010-04-22 09:37:16.000 Scaling the control recipe OSS10131\bep
2010-04-22 09:37:17.000 released NULL
2010-04-22 09:37:34.000 running NULL
2010-04-22 14:43:51.000 completed NULL
2010-04-22 16:50:12.000 Close batch OSS10131\v056
2010-04-22 16:56:37.000 Batch closed (CDV)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 10:45:44

You are using XML namespace, so yo have to write like this

DECLARE	@Sample XML
 
SET	@Sample = '
<Archivebatch pcellid="0" pcellname="Plant" xmlns="SIMATIC_BATCH_V7_0_1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlversion="7.0.1.0" dbident="SB6_221-96-2936103" time="2010-05-20T16:14:42.609Z">
  <Cr id="132" name="WXTQ153 25%" creationdate="2010-04-22T09:36:25.000Z" modifieddate="2010-04-22T16:56:36.000Z" quantity="1" formulacategoryname="Production" formulaname="Production">
    <Modifcltn>
      <Modif id="1" modiftype="12" time="2010-04-22T09:37:07.000Z" desc="Batch created ( ID: 132 / 59 ), Name: WXTQ153 25%" loginname="OSS10131\bep" fullloginname="Bo Petersen" computername="OSS10131" />
      <Modif id="2" modiftype="2" time="2010-04-22T09:37:16.000Z" desc="Scaling the control recipe" loginname="OSS10131\bep" fullloginname="Bo Petersen" computername="OSS10131" />
      <Modif id="3" modiftype="29" time="2010-04-22T09:37:17.000Z" desc="released" computername="OSS10131" />
      <Modif id="4" modiftype="29" time="2010-04-22T09:37:34.000Z" desc="running" computername="OSS10131" />
      <Modif id="5" modiftype="29" time="2010-04-22T14:43:51.000Z" desc="completed" computername="OSS10131" />
      <Modif id="6" modiftype="29" time="2010-04-22T16:50:12.000Z" desc="Close batch" loginname="OSS10131\v056" fullloginname="v056" computername="OSS10131" />
      <Modif id="7" modiftype="29" time="2010-04-22T16:56:37.000Z" desc="Batch closed" loginname="(CDV) " fullloginname="BATCH CDV" computername="OSS10131" />
    </Modifcltn>
  </Cr>
</Archivebatch>
'
-- Solution here by Peso
;WITH XMLNAMESPACES('SIMATIC_BATCH_V7_0_1' AS theYak)
SELECT	n.value('@time', 'DATETIME') AS theTime,
	n.value('@desc', 'VARCHAR(MAX)') AS Descr,
	n.value('@loginname', 'VARCHAR(MAX)') AS loginname
FROM	@Sample.nodes('/theYak:Archivebatch/theYak:Cr/theYak:Modifcltn/theYak:Modif') AS x(n)

N 56°04'39.26"
E 12°55'05.63"

cpender
Starting Member

11 Posts

Posted - 2010-07-06 : 11:02:27

Thanks Peso, that works.

But how would that code differ if I was querying a column of a table like in my code above?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 11:08:50

No difference since you use the DEFAULT keyword, which is not necessary.

DECLARE	@Sample TABLE
	(
		ID INT,
		Data XML
	)

INSERT	@Sample
	(
		ID,
		Data
	)
SELECT	132,
	'
		<Archivebatch pcellid="0" pcellname="Plant" xmlns="SIMATIC_BATCH_V7_0_1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlversion="7.0.1.0" dbident="SB6_221-96-2936103" time="2010-05-20T16:14:42.609Z">
		  <Cr id="132" name="WXTQ153 25%" creationdate="2010-04-22T09:36:25.000Z" modifieddate="2010-04-22T16:56:36.000Z" quantity="1" formulacategoryname="Production" formulaname="Production">
		    <Modifcltn>
		      <Modif id="1" modiftype="12" time="2010-04-22T09:37:07.000Z" desc="Batch created ( ID: 132 / 59 ), Name: WXTQ153 25%" loginname="OSS10131\bep" fullloginname="Bo Petersen" computername="OSS10131" />
		      <Modif id="2" modiftype="2" time="2010-04-22T09:37:16.000Z" desc="Scaling the control recipe" loginname="OSS10131\bep" fullloginname="Bo Petersen" computername="OSS10131" />
		      <Modif id="3" modiftype="29" time="2010-04-22T09:37:17.000Z" desc="released" computername="OSS10131" />
		      <Modif id="4" modiftype="29" time="2010-04-22T09:37:34.000Z" desc="running" computername="OSS10131" />
		      <Modif id="5" modiftype="29" time="2010-04-22T14:43:51.000Z" desc="completed" computername="OSS10131" />
		      <Modif id="6" modiftype="29" time="2010-04-22T16:50:12.000Z" desc="Close batch" loginname="OSS10131\v056" fullloginname="v056" computername="OSS10131" />
		      <Modif id="7" modiftype="29" time="2010-04-22T16:56:37.000Z" desc="Batch closed" loginname="(CDV) " fullloginname="BATCH CDV" computername="OSS10131" />
		    </Modifcltn>
		  </Cr>
		</Archivebatch>
	'

-- Solution here by Peso
;WITH XMLNAMESPACES('SIMATIC_BATCH_V7_0_1' AS c)
SELECT		n.value('@time', 'DATETIME') AS theTime,
		n.value('@desc', 'NVARCHAR(50)') AS Descr,
		n.value('@loginname', 'NVARCHAR(50)') AS loginname
FROM		@Sample AS s
CROSS APPLY	Data.nodes('/c:Archivebatch/c:Cr/c:Modifcltn/c:Modif') AS x(n)
WHERE		s.ID = 132

N 56°04'39.26"
E 12°55'05.63"

cpender
Starting Member

11 Posts

Posted - 2010-07-06 : 11:19:19

Thanks, once again Peso. That works on your example.

.......but (sorry) If the table, "tblBatches" already exists and there is a coulmn "XMLData" and in the row with ID=132 the "XMLData" column contains the above XML, how would I structure my query?

I've tried a few edits on your script but can't seem to get the syntax right.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 12:06:16

[code]-- Solution here by Peso
;WITH XMLNAMESPACES('SIMATIC_BATCH_V7_0_1' AS y)
SELECT n.value('@time', 'DATETIME') AS theTime,
n.value('@desc', 'NVARCHAR(50)') AS Descr,
n.value('@loginname', 'NVARCHAR(50)') AS loginname
FROM tblBatches AS s
CROSS APPLY XmlData.nodes('/y:Archivebatch/y:Cr/c:Modifcltn/y:Modif') AS x(n)
WHERE s.ID = 132[/code]

N 56°04'39.26"
E 12°55'05.63"

cpender
Starting Member

11 Posts

Posted - 2010-07-06 : 12:15:06

That's exactly what I tried. I get the following error:

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 14:05:57

It means your XmlData column in table tblBatches IS NOT OF XML DATATYPE.
Most probably the column is NTEXT, NVARCHAR(MAX) or similar.

Please recheck!

N 56°04'39.26"
E 12°55'05.63"

cpender
Starting Member

11 Posts

Posted - 2010-07-07 : 04:10:44

My apologies Peso. The table name that I should be querying was in fact "tblBatches_XML".

The reasoning for this is that in the original "tblBatches" table the data was in BLOBData format which is then casted into XML datatype and inserted into the "tblBatches_XML" table.

The script works perfectly. My apologies for wasting your time.

Thanks again, you've been extremely helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources