XML query help - 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 2005 Forums

Transact-SQL (2005)

XML query help

Author

Topic

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-22 : 04:52:09

Hi i have following input

<ROOT>
<PurchaseOrderReference PurchaseOrderReferenceType="ISBN13" AssignedBy="Buyer">9780618146666</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="Copyright" AssignedBy="Buyer">2002</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="CustomerSpecificationNumber" AssignedBy="Buyer">9780618146666</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="TransactionID">763</PurchaseOrderReference>
</ROOT>

i want to get the value "763", i,e for Transactionid

How to do this for all rows in a table having data like above xml

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 06:40:00

try this:-

SELECT
PurchaseOrderReference.TranID.value('./[@PurchaseOrderReferenceType="TransactionID"]','int')
FROM
(SELECT
xml.nodes('/ROOT/PurchaseOrderReference')
FROM Table)
as PurchaseOrderReference(TranID)

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-22 : 08:51:00

Hi,

when i execute your stmt i am getting error,
my query is

DECLARE @R TABLE ( XM XML)

INSERT INTO @R
SELECT '<ROOT>
<PurchaseOrderReference PurchaseOrderReferenceType="ISBN13" AssignedBy="Buyer">9780618146666</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="Copyright" AssignedBy="Buyer">2002</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="CustomerSpecificationNumber" AssignedBy="Buyer">9780618146666</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="TransactionID">763</PurchaseOrderReference>
</ROOT>' UNION ALL
SELECT '<ROOT>
<PurchaseOrderReference PurchaseOrderReferenceType="ISBN13" AssignedBy="Buyer">9780618146666</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="Copyright" AssignedBy="Buyer">2002</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="CustomerSpecificationNumber" AssignedBy="Buyer">9780618146666</PurchaseOrderReference>
<PurchaseOrderReference PurchaseOrderReferenceType="TransactionID">763</PurchaseOrderReference>
</ROOT>'

SELECT
PurchaseOrderReference.TranID.value('./[@PurchaseOrderReferenceType="TransactionID"]','int')
FROM
(
SELECT XM.nodes('/ROOT/PurchaseOrderReference')
FROM @R
) as PurchaseOrderReference(TranID)

error i got :-
Msg 227, Level 15, State 1, Line 18
"nodes" is not a valid function, property, or field.

Mark C
Starting Member

1 Post

Posted - 2008-02-23 : 02:56:38

Maybe this

SELECT
PurchaseOrderReference.TranID.value('.','int')
FROM @R
CROSS APPLY XM.nodes('/ROOT/PurchaseOrderReference[@PurchaseOrderReferenceType="TransactionID"]')
as PurchaseOrderReference(TranID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources