XQuery syntax to flatten Xml

Question

Post reply

XQuery syntax to flatten Xml

TT-131210

SSCommitted

Points: 1603
More actions
August 5, 2009 at 3:53 pm

#140569

I am having problems trying to flatten the XML below. I cannot seem to figure how to get the LON show up.
DECLARE @xml xml
SET @xml = '
123456
TestName1
TestValue1
987654
TestName2
TestValue2
TestName3
TestValue3
'
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT,@xml
SELECT *
FROM OPENXML (@docHandle, 'Batch/OrderRequest/document/Field', 0)
WITH ( Name varchar(max) 'Name/text()',
Value varchar(max) 'Value/text()',
LON varchar(max) '/loanNumber')
Desired Results:
NameValueLON
TestName1TestValue1123456
TestName2TestValue2987654
TestName3TestValue3987654
Thanks Tim

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Two key points:

- use the .nodes() method to split the xml doc into a rowset of BATCH/ORDERREQUEST/DOCUMENT/FIELD nodes

- use ../../ to crawl up the tree from those nodes to get the loan number:

DECLARE @xml xml

SET @xml = '

123456

TestName1

TestValue1

987654

TestName2

TestValue2

TestName3

TestValue3

'

SELECT OrdRqs.OrderRequests.value('(./NAME/text())[1]', 'varchar(16)') AS [Name],

OrdRqs.OrderRequests.value('(./VALUE/text())[1]', 'varchar(16)') AS [Value],

OrdRqs.OrderRequests.value('(../../LOANNUMBER/text())[1]', 'int') AS LON,

OrderRequests.query('.') -- this last field included to show the result of the nodes() method

FROM @xml.nodes('BATCH/ORDERREQUEST/DOCUMENT/FIELD') AS OrdRqs(OrderRequests)

Eddie Wuerch
MCM: SQL

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

I didn’t know that I can get to an element that is not part of the XML that the nodes method produces. Although I’m not the original poster, this answer can help me a lot in a project that I’m involved right now.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 3

SELECT Name = A.value('name[1]', 'varchar(50)'),

Value = A.value('value[1]', 'varchar(50)'),

LON = A.value('parent::document/parent::orderrequest/loannumber[1]', 'int')

FROM @xml.nodes('/batch/orderrequest/document/field') N (A)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Avoiding the parent axis as mentioned here may improve performance

http://msdn.microsoft.com/en-us/library/ms345118(SQL.90).aspx

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

Lnum.value('.', 'int') AS LON

FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)

CROSS APPLY Breq.nodes('LOANNUMBER') AS L(Lnum)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD') AS D(Doc)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

TT-131210 SSCommitted Points: 1603 More actions · Answer 5

TT-131210

SSCommitted

Points: 1603

August 10, 2009 at 7:52 am

#1036794

Thanks this is exactly what I needed.

Tim

TT-131210 SSCommitted Points: 1603 More actions · Answer 6

I thought the response earlier would have worked for what I wanted but looking into my xml it did not. I need to move the loannumber into the fields element and still have it produce the results below. Is it possible to do this with CASE statement or do I need something else.

Tim

DECLARE @xml xml

SET @xml = '

TestName1

TestValue1

loannumber

123456

loannumber

123456

TestName2

TestValue2

TestName3

TestValue3

loannumber

987654

'

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

CASE WHEN Doc.value('Name[1]', 'varchar(200)') = 'loannumber' THEN Convert(varchar(max), Doc.value('Value[1]', 'varchar(max)')) END AS loannumber

FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD') AS D(Doc)

Desired Results:

Name Value LoanNumber

TestName1 TestValue1 123456

TestName2 TestValue2 987654

TestName3 TestValue3 987654

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

Try this

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

Num.value('.','int') AS LoanNumber

FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD[NAME/text()!="loannumber"]') AS D(Doc)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber"]') AS L(Num)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

TT-131210 SSCommitted Points: 1603 More actions · Answer 8

Is there a trim function that I could use for the "loannumber"?

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

Num.value('.','int') AS LoanNumber

FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD[NAME/text()!="loannumber"]') AS D(Doc)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber"]') AS L(Num)

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 9

TT (8/13/2009)
Is there a trim function that I could use for the "loannumber"?
SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],
Doc.value('VALUE[1]', 'varchar(16)') AS [Value],
Num.value('.','int') AS LoanNumber
FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)
CROSS APPLY Breq.nodes('DOCUMENT/FIELD[NAME/text()!="loannumber"]') AS D(Doc)
CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber"]') AS L(Num)

Not as far as I know. However, you could do this

LTRIM(RTRIM(Num.value('.','varchar(100)'))) AS LoanNumber

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

TT-131210 SSCommitted Points: 1603 More actions · Answer 10

I am looking to trim in the CROSS APPLY part. I getting XML that has a space after the value "loannumber" value in the NAME element.

CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber"]') AS L(Num)

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 11

TT (8/13/2009)
I am looking to trim in the CROSS APPLY part. I getting XML that has a space after the value "loannumber" value in the NAME element.
CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber"]') AS L(Num)

If it's just a single space you could do this

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

Num.value('.','int') AS LoanNumber

FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD[NAME/text()!="loannumber" and NAME/text()!="loannumber "]') AS D(Doc)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()="loannumber" or ../NAME/text()="loannumber "]') AS L(Num)

Possibly you could use "contains" as well

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

TT-131210 SSCommitted Points: 1603 More actions · Answer 12

It is not guaranteed that it will only be one space. So I tried the contains function and that should work.

Thanks Tim

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

Num.value('.','int') AS LoanNumber

FROM @xml.nodes('/BATCH/ORDERREQUEST') AS B(Breq)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD[NAME/text()!="loannumber" and NAME/text()!="loannumber "]') AS D(Doc)

CROSS APPLY Breq.nodes('DOCUMENT/FIELD/VALUE[../NAME/text()[contains(.,"loannumber")]]') AS L(Num)