Avatar of WvrLthr
WvrLthr
Flag for United States of America asked on

SQL Server query with no namespace in schema

Hello EE,

We have a third party application that stores data in an XML column in SQL Server 2005.  The schema is defined, but there is no namespace defined in their system.  I need to do some reporting on this data.  I am new to XML schemas.  In the search and work I have done on Adventureworks and other examples, there is always a namespace defined in the schema and used in the SQL query.

I have attached a sample transaction and the schema they have defined.  My question for you is what is the syntax to select data from this sample transaction?  An example would be great.  For example, to list the quantities picked (Field name "Pick Quantity").

I have attached the schema and transaction.

Thank you for your help.
xsd-schema.doc
transaction-xml.doc
Web Languages and StandardsMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
WvrLthr

8/22/2022 - Mon
Mark Wills

Well, there is an error in the doc.

<ERPPwd>a23hg7890@ERPPwd>     should be    <ERPPwd>a23hg7890</ERPPwd>


Seems to be multiple pick quantities, so, what about something like ;

   select convert(int,x.c.value('@value', 'varchar(50)')) as PickQuantity
   from   my_table t
   cross apply t.my_xml_columne.nodes('XYZTransaction/Transactions/Transaction/Tx/Fields/Field') x(c)
   where x.c.value('@name', 'varchar(50)') = 'PickQuantity'

--or accumulate quanties for that row maybe ?

   select SUM(case when x.c.value('@name', 'varchar(50)') = 'PickQuantity' then convert(int,x.c.value('@value', 'varchar(50)')) else 0 end) as PickQuantity
   from   my_table t
   cross apply t.my_xml_columne.nodes('XYZTransaction/Transactions/Transaction/Tx/Fields/Field') x(c)

Open in new window

WvrLthr

ASKER
Mark,

Thank you for the reply.  First off, your eagle eye caught an error that really isn't a doc error but a user error.  I was dummying out the sensitive information and happened to end the password gibberish with an @.

Wow.  those are some complicated looking queries.  I ran the second one with a Top 1 and it worked, so you definitely have me on the right track.  Now, if I can ask three more questions:

1.  How would you add criteria to pull the second query specifying a specific pickslip number? -- so I can pull a specific record, not all 600k+ records in the database.
2.  How would you add criteria to pull the second query specifying the user? -- Curious to see the difference in syntax for an element that does not have a field name
3.  Most important, can you point me to a good resource to learn how to construct this type of query?  I have not seen anything in my research using cross apply, for example.  I really want to learn this and be proficient.

Thank you again for all your help.

Jeremy
WvrLthr

ASKER
Oops - just caught the fact that I also overwrote the opening </ on the password also.  No wonder you thought that was an error.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
WvrLthr

ASKER
Mark,

The good news is the query selecting on pickslip got results.  The bad news is it took over 6 minutes and blocked the Production transactions from the warehouse!

Thank you very much for your excellent samples and the pointer to beyondrelational.com.  It looks like a really useful site.

Jeremy
WvrLthr

ASKER
The solution was well done with timely responses to additional questions.
Mark Wills

>> Took over 6 minutes...


Ouch... If you can use some filtering (like a where clause) on the actual table to cut down the number of rows.

Is there anything else in the XML that can be filtered out ?

Also, if read only, then use the WITH (NOLOCK) hint after the table name before the cross join e.g.

   select det.value('(Field[@name="PickSlipNumber"]/@value)[1]', 'varchar(50)') as PickSlipNumber,
          det.value('(Field[@name="PickQuantity"]/@value)[1]', 'INT') as PickQuantity
   from   #my_table tbl with (nolock)
   cross  apply tbl.my_xml_column.nodes('XYZTransaction[User="UserA"]/Transactions/Transaction/Tx/Fields') fld(det) 

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WvrLthr

ASKER
Mark,

Oh wow.  That is a great tip on with nolock.  Yes, all I want to do is read only reporting queries.  Can I please ask one more question.  Your point about where clause on the actual table.  I assume you mean non XML columns.  Well, there are nonXML columns that could be useful.  For example, workflowid is a separate nvarchar(50) column on the table.  How would I add criteria of "workflowid='picking'" to the query you have above with the where clause for the pickslip number?  Curious to know syntax for having a where clause on the XML and on another table column.

Thanks again for more great insight.  I got so focused on the XML column that I forgot to look at other columns on the table.

Jeremy
Mark Wills

You just include as needed....

e.g.

   select tbl.*,
          det.value('(Field[@name="PickSlipNumber"]/@value)[1]', 'varchar(50)') as PickSlipNumber,
          det.value('(Field[@name="PickQuantity"]/@value)[1]', 'INT') as PickQuantity
   from   #my_table tbl with (nolock)
   cross  apply tbl.my_xml_column.nodes('XYZTransaction[User="UserA"]/Transactions/Transaction/Tx/Fields') fld(det) 
   where  tbl.workflowid = 'picking'

Open in new window

WvrLthr

ASKER
Mark,

Thanks for the continued work on this question.  I don't think I was specific enough in my last post.  How would I add the workflowid WHERE clause on this query you created earlier.  
Jeremy

select * from (
   select hdr.value('@Sequence', 'varchar(50)') as Seq,
          col.value('(Field[@name="PickSlipNumber"]/@value)[1]', 'varchar(50)') as PickSlipNumber,
          col.value('(Field[@name="PickQuantity"]/@value)[1]', 'INT') as PickQuantity
   from   #my_table tbl
   cross apply tbl.my_xml_column.nodes('XYZTransaction[User="UserA"]/Transactions/Transaction/Tx') tx(hdr)
   cross apply hdr.nodes('./Fields') fld(col)
) s
where pickslipnumber = '88957'
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Wills

Sorry about the laggard response, must have overlooked in the email queue...

So, using the double cross apply will slow things down quite a bit. Anyway, I would put the where clause inside the subquery to try to cut down the number of rows used in the cross joins. e.g.

select * from (
   select hdr.value('@Sequence', 'varchar(50)') as Seq,
          col.value('(Field[@name="PickSlipNumber"]/@value)[1]', 'varchar(50)') as PickSlipNumber,
          col.value('(Field[@name="PickQuantity"]/@value)[1]', 'INT') as PickQuantity
   from   #my_table tbl  with (nolock)
   cross apply tbl.my_xml_column.nodes('XYZTransaction[User="UserA"]/Transactions/Transaction/Tx') tx(hdr)
   cross apply hdr.nodes('./Fields') fld(col)
 
   where  tbl.workflowid = 'picking' 

) s
where pickslipnumber = '88957' 

Open in new window

WvrLthr

ASKER
Mark,

Thanks again for hanging with me on this issue.  I have to apologize for not posting that I was going to be on vacation last week.  I plan to test some of this today or tomorrow after I dig out a bit.